PostgreSQL
From Knowledge
- SET PASSWORD FOR ADMIN (USER POSTGRES)
ALTER USER postgres with encrypted password 'xxxxxxx';
- CREATE USER
CREATE ROLE jdoe LOGIN INHERIT;
- CREATE GROUP
CREATE ROLE departmentHumanResource NOINHERIT;
- ADD USER TO GROUP
GRANT departmentHumanResource TO jdoe;
- GRANT GROUP TO DATABASE
GRANT CREATE, CONNECT, TEMP ON DATABASE abnahme TO GROUP sandbox;
- GRANT GROUP TO SCHEMA
GRANT CREATE, USAGE ON SCHEMA test TO GROUP sandbox;
- GRANT GROUP TO SEQUENCES
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA test TO GROUP sandbox;
- GRANT GROUP TO TABLES
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA test TO GROUP sandbox;
- REMOVE USER FROM GROUP
REVOKE departmentHumanResource FROM jdoe;
- REMOVE USER OR GROUP
DROP ROLE jdoe; DROP ROLE departmentHumanResource;
- CREATE SEQUENCE
CREATE SEQUENCE schema.seq_phonebook;
- CREATE TABLE
CREATE TABLE schema.foobar ... id INTEGER DEFAULT NEXTVAL('schema.seq_phonebook') - CREATE FUNCTION
CREATE FUNCTION schema.addition(val1 integer, val2 integer) RETURNS integer
AS $$
DECLARE
total integer;
BEGIN
total := val1 + val2;
RETURN total;
END;
$$
LANGUAGE PLPGSQL;
