postgresql
Gestión de roles

Sintaxis

• 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

Crear un usuario con una contraseña.

En general, debe evitar usar la función de base de datos predeterminada (a menudo postgres ) en su aplicación. En su lugar, debe crear un usuario con niveles más bajos de privilegios. Aquí hacemos uno llamado niceusername y le damos una contraseña very-strong-password

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

El problema con eso es que las consultas escritas en la consola psql se guardan en un archivo histórico .psql_history en el directorio de inicio del usuario y también pueden registrarse en el registro del servidor de base de datos PostgreSQL, exponiendo así la contraseña.

Para evitar esto, use el comando \password para establecer la contraseña del usuario. Si el usuario que emite el comando es un superusuario, no se solicitará la contraseña actual. (Debe ser superusuario para alterar contraseñas de superusuarios)

CREATE ROLE niceusername with LOGIN;
\password niceusername

Crear rol y base de datos coincidentes

Para admitir una aplicación determinada, a menudo creas un nuevo rol y una base de datos para que coincida.

Los comandos de shell para ejecutar serían estos:

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

$ createdb -O blogger blogger

Esto supone que pg_hba.conf se ha configurado correctamente, lo que probablemente tenga este aspecto:

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

Otorgar y revocar privilegios.

Supongamos que tenemos tres usuarios:

1. El administrador de la base de datos> admin
2. La aplicación con acceso completo para sus datos> read_write
3. El acceso de solo lectura> read_only

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

Con las consultas anteriores, los usuarios que no son de confianza ya no pueden conectarse a la base de datos.

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

El siguiente conjunto de consultas revoca todos los privilegios de usuarios no autenticados y proporciona un conjunto limitado de privilegios para el usuario 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;

Modificar la ruta de búsqueda predeterminada del usuario

Con los siguientes comandos, se puede establecer la ruta de búsqueda predeterminada del usuario.

1. Compruebe la ruta de búsqueda antes de establecer el esquema predeterminado.

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. Configure search_path con alter user comando alter user para agregar un nuevo esquema 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. Verifique el resultado después de la ejecución.

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)

Alternativa:

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

Otorgar privilegios de acceso a objetos creados en el futuro.

Supongamos que tenemos three users :

1. El administrador de la base de datos> admin
2. La aplicación con acceso completo para sus datos> read_write
3. El acceso de solo lectura> read_only

Con las siguientes consultas, puede establecer privilegios de acceso en objetos creados en el futuro en un esquema específico.

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;

O bien, puede establecer privilegios de acceso en objetos creados en el futuro por un usuario específico.

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

Crear usuario de solo lectura

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;