postgresql
Rolbeheer

Syntaxis

• 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

Maak een gebruiker met een wachtwoord aan

Over het algemeen moet u vermijden de standaarddatabaserol (vaak postgres ) in uw toepassing te gebruiken. U moet in plaats daarvan een gebruiker met lagere bevoegdheden maken. Hier maken we er een genaamd niceusername en geven het een wachtwoord very-strong-password

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

Het probleem daarmee is dat query's die in de psql console worden getypt, worden opgeslagen in een geschiedenisbestand .psql_history in de .psql_history van de gebruiker en mogelijk ook worden vastgelegd in het logboek van de PostgreSQL-databaseserver, waardoor het wachtwoord wordt blootgelegd.

Om dit te voorkomen, gebruikt u de opdracht \password om het gebruikerswachtwoord in te stellen. Als de gebruiker die de opdracht geeft een superuser is, wordt het huidige wachtwoord niet gevraagd. (Moet superuser zijn om wachtwoorden van superusers te wijzigen)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Maak een rol en bijpassende database

Om een bepaalde toepassing te ondersteunen, maakt u vaak een nieuwe rol en database om aan te passen.

De shell-opdrachten die moeten worden uitgevoerd, zijn de volgende:

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

$ createdb -O blogger blogger

Dit veronderstelt dat pg_hba.conf correct is geconfigureerd, wat er waarschijnlijk als volgt uitziet:

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

Bevoegdheden verlenen en intrekken.

Stel dat we drie gebruikers hebben:

1. De beheerder van de database> admin
2. De applicatie met volledige toegang voor haar gegevens> read_write
3. De alleen-lezen toegang> alleen lezen

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

Met de bovenstaande vragen kunnen niet-vertrouwde gebruikers geen verbinding meer maken met de database.

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

De volgende reeks vragen trekt alle rechten van niet-geverifieerde gebruikers in en biedt een beperkte reeks rechten voor de gebruiker read_write .

--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;

Wijzig het standaard zoekpad van de gebruiker

Met de onderstaande opdrachten kan het standaardzoekpad van de gebruiker worden ingesteld.

1. Controleer het zoekpad voordat u het standaardschema instelt.

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. Stel search_path met opdracht alter user om een nieuw schema my_schema toe te voegen

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. Controleer het resultaat na uitvoering.

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)

Alternatief:

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

Verleen toegangsrechten voor objecten die in de toekomst zijn gemaakt.

Stel dat we three users :

1. De beheerder van de database> admin
2. De applicatie met volledige toegang voor haar gegevens> read_write
3. De alleen-lezen toegang> read_only

Met onderstaande query's kunt u toegangsrechten instellen voor objecten die in de toekomst in een bepaald schema worden gemaakt.

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;

Of u kunt toegangsrechten instellen voor objecten die in de toekomst door de opgegeven gebruiker worden gemaakt.

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

Creëer alleen-lezen gebruiker

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;