postgresql
Rollhantering

Syntax

• CREATE ROLE name [ [ WITH ] option [ ... ] ]

• CREATE USER name [ [ WITH ] option [ ... ] ]

• where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

Skapa en användare med ett lösenord

Generellt sett bör du undvika att använda standarddatabasrollen (ofta postgres ) i din applikation. Du bör istället skapa en användare med lägre behörighetsnivåer. Här skapar vi ett kallat niceusername och ger det ett lösenord very-strong-password

CREATE ROLE niceusername with PASSWORD 'very-strong-password' LOGIN;

Problemet med det är att frågor som skrivs in i psql konsolen sparas i en historikfil .psql_history i användarens .psql_history och kan lika gärna loggas till PostgreSQL-databasserverloggen och därmed avslöja lösenordet.

För att undvika detta, använd kommandot \password att ställa in användarlösenordet. Om användaren som utfärdar kommandot är en superanvändare kommer det aktuella lösenordet inte att ställas. (Måste vara superanvändare för att ändra lösenord för superanvändare)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Skapa roll och matchande databas

För att stödja en given applikation skapar du ofta en ny roll och en databas som ska matchas.

Skalkommandona att köra skulle vara dessa:

$ createuser -P blogger
Enter password for the new role: ********
Enter it again: ********

$ createdb -O blogger blogger

Detta antar att pg_hba.conf har konfigurerats korrekt, vilket förmodligen ser ut så här:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    sameuser        all             localhost               md5
local   sameuser        all                                     md5

Bevilja och återkalla privilegier.

Anta att vi har tre användare:

1. Administratören av databasen> admin
2. Applikationen med full åtkomst för hennes data> read_write
3. Den skrivskyddade åtkomst> read_only

--ACCESS DB
REVOKE CONNECT ON DATABASE nova FROM PUBLIC;
GRANT  CONNECT ON DATABASE nova  TO user;

Med ovanstående frågor kan inte betrodda användare inte längre ansluta till databasen.

--ACCESS SCHEMA
REVOKE ALL     ON SCHEMA public FROM PUBLIC;
GRANT  USAGE   ON SCHEMA public  TO user;

Nästa uppsättning frågor återkallar alla behörigheter från oautentiserade användare och ger begränsade uppsättningar med behörigheter för read_write användaren.

--ACCESS TABLES
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;
GRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;
GRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;

--ACCESS SEQUENCES
REVOKE ALL   ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only; -- allows the use of CURRVAL
GRANT UPDATE ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of NEXTVAL and SETVAL
GRANT USAGE  ON ALL SEQUENCES IN SCHEMA public TO read_write; -- allows the use of CURRVAL and NEXTVAL
GRANT ALL    ON ALL SEQUENCES IN SCHEMA public TO admin;

Ändra användarsökets sökväg

Med kommandona nedan kan användarens standardsökväg ställas in.

1. Kontrollera sökvägen innan du anger standardschema.

postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

2. Ställ search_path med alter user att lägga till ett nytt schema my_schema

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter user user1 set search_path='my_schema, "$user", public';
ALTER ROLE

3. Kontrollera resultatet efter körning.

postgres=# \c postgres user1
Password for user user1: 
You are now connected to database "postgres" as user "user1".
postgres=> show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Alternativ:

postgres=# set role user1;
postgres=# show search_path;
 search_path 
-------------
 my_schema, "$user", public
(1 row)

Ge åtkomstbehörigheter för objekt som skapats i framtiden.

Anta att vi har three users :

1. Administratören av databasen> admin
2. Applikationen med full åtkomst för hennes data> read_write
3. Den skrivskyddade åtkomst> read_only

Med nedanstående frågor kan du ange åtkomstbehörigheter för objekt som skapats i framtiden i ett specificerat schema.

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT                      ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL                         ON TABLES TO admin;

Du kan också ange åtkomstbehörigheter för objekt som skapats i framtiden av en angiven användare.

ALTER DEFAULT PRIVILEGES FOR ROLE admin GRANT SELECT  ON TABLES TO read_only;

Skapa läsbar användare

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;

GRANT USAGE ON SCHEMA public to readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;