PostgreSQL security permissions grants groups role users - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/predefined-roles.html
• https://wiki.postgresql.org/images/d/d1/Managing_rights_in_postgresql.pdf

w rolconfig czy pgadiw wlaczony czy ustawiony search path

select rolname,rolconfig from pg_roles where rolname in ('ebkadm');

 User
-----------------------
Host Based Access
----------------------
Database (CONNECT)
----------------------
Schema (USAGE)
----------------------
Tables | Views| other objects

Going through to a relation

• Provides HBA says ok and the database allows connections, the role:

1. Must have the CONNECT right to the database
2. Must have the USAGE on the schema containing the object
3. Must have the ownership or right to access or modify the contents of the relation

• Users in PostgreSQL exist at the instance level.If we create a user, it is not just visible inside one database; it can be seen by all the databases. A user might have permission to access just a single database, but users are essentially created at the instance level.

• Users and roles are the same things. The CREATE ROLE and CREATE USER clauses have different default values (the only difference being that roles do not get the LOGIN attribute by default), but, at the end of the day, users and roles are the same. Therefore, the CREATE ROLE and CREATE USER clauses support the very same syntax.

• Note that when somebody creates a database, this user will automatically be the owner of the database. The rule is this: the creator is always automatically the owner of an object (unless specified otherwise, as can be done with the CREATE DATABASE clause).

• The CREATEROLE or NOCREATEROLE clause defines whether somebody is allowed to create new users/roles.

• The INHERIT or NOINHERIT - if the INHERIT clause is set (which is the default value), a user can inherit permissions from some other user. Using inherited permissions allows us to use roles, which is a good way to abstract permissions.

• The LOGIN, or NOLOGIN, clause defines whether a role is allowed to log in to the instance.Note that the LOGIN clause is not enough to actually connect to a database. For ROLE default option is NOLOGIN.

bookkeeper might be marked as NOLOGIN. People as the LOGIN clause, but can inherit all the permissions from the bookkeeper role.

• https://www.postgresql.org/docs/current/sql-createrole.html
• https://www.postgresql.org/docs/current/sql-droprole.html

CREATE ROLE  bookkeeper NOLOGIN;  
CREATE ROLE  joe LOGIN;  # or CREATE USER joe  
GRANT  bookkeeper TO joe;  # bookkeeper role is assigned to the joe role, so that they can do everything bookkeeper is actually allowed to do
ALTER  ROLE  joe PASSWORD 'abc';  
GRANT ROLE group_name TO user_name;  # To add a role to another

Be aware of the fact that ALTER ROLE changes the attributes of a role. PASSWORD will actually make the password show up in the log file if Data Definition Language (DDL) logging has been configured. This is not too desirable. It is better to change the password using a visual tool.

• PostgreSQL provides a feature called Row Level Security (RLS). The idea is that we can exclude rows from the scope of a user. If a user is explicitly supposed to bypass RLS, set this value to BYPASSRLS. The default value is NOBYPASSRLS.

• CONNECTION LIMIT <n> - restrict the number of connections allowed for a user. Note that, overall, there can never be more connections than defined in the postgresql.conf file (max_connections). However we can always restrict certain users to a lower value.

• PASSWORD NULL explicitly forces a null (not empty) password, preventing the user from logging in with any password. This option can be used to deny password-based authentication.

• VALID UNTIL allows you to specify an instant (in the future) when the role will expire.

• PASSWORD or ENCRYPTED PASSWORD are equivalent options and allow you to set the login password for the role. Cluster always stores role passwords in an encrypted form, so the use of ENCRYPTED PASSWORD does not add any value to the PASSWORD option.

CREATE ROLE luca
WITH LOGIN PASSWORD 'xxx'
VALID UNTIL '2030-12-25 23:59:59';

• Chane user settings

ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] 
              SET configuration_parameter { TO | = } { value | DEFAULT } 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] 
              SET configuration_parameter FROM CURRENT 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] RESET configuration_parameter 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] RESET ALL

ALTER  ROLE  joe SET TimeZone = 'UTC-4';  # As soon as joe reconnects, the time zone will already be set for him.

Every group can have one or more admin members, which are allowed to add new members to the group. The ADMIN option allows a user to specify the member that will be associated as an administrator of the newly created group.

CREATE ROLE book_reviewers
WITH NOLOGIN
ADMIN luca;

Other option is GRANT WITH ADMIN OPTION clause allows the mem- bership of a role with administrative privileges.

GRANT book_reviewers
TO enrico
WITH ADMIN OPTION;

Inspecting role

SELECT current_role; 

List all roles in system

\du

# Query default access privileges of users
SELECT r.rolname, 
       r.rolsuper, 
       r.rolinherit, 
       r.rolcreaterole, 
       r.rolcreatedb, 
       r.rolcanlogin, 
       r.rolconnlimit, 
       r.rolvaliduntil, 
       ARRAY(SELECT b.rolname 
               FROM pg_catalog.pg_auth_members m 
               JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
               WHERE m.member = r.oid) as memberof , 
       r.rolreplication , 
       r.rolbypassrls 
FROM pg_catalog.pg_roles r 
WHERE r.rolname !~ '^pg_' 
ORDER BY 1;

Specific role - not show password - only ****

• https://www.postgresql.org/docs/current/view-pg-roles.html

rolconnlimit, rolpassword
FROM pg_roles
WHERE rolname = 'luca';

The special catalog pg_authid represents the backbone for the pg_roles information, and can be queried with the very same statement, but reports the user password (as encrypted text). pg_authid can be queried only by superusers

• https://www.postgresql.org/docs/current/catalog-pg-authid.html

SELECT rolname, rolcanlogin, rolconnlimit, rolpassword
FROM pg_authid WHERE rolname = 'luca';

• By default postgres, without a password (!)
• Can by given to any role using the SUPERUSER attribute:

ALTER ROLE ROLE role_name SUPERUSER;
ALTER ROLE role_name NOSUPERUSER;

• Superuser are god on the cluster, but:
• They must pass through Host Based Access (pg_hba.conf)
• They cannot connect to a database with datallowconn set to false

• An implicit group everybody belongs to
• Has some default rights granted

• Allow a role to get the rights of other roles granted to it directly or not • Use of SET ROLE to obtain rights from other roles • Protect the role from having too many rights all the time

How to delegate superuser privileges without giving the password of postgres to others:

• Create a admins group with inheritance :

CREATE ROLE admins NOLOGIN NOINHERIT;

• Create a admin account with no superuser rights:

CREATE ROLE one_admin LOGIN PASSWORD 'foobar';

• Put one_admin into admins group:

GRANT admins TO one_admin;

• Put admins into postgres:

GRANT postgres TO admins;

Default rights After initdb:

• Local access only (listen_addresses, pg_hba.conf)
• Right to connect to any database but template0
• CONNECT : connect to the database
• TEMP : create temporary tables
• Rights on the public schema
• USAGE : access the objects
• CREATE : create new objects ⇒ Those default rights are granted to PUBLIC

• Configuration done in pg_hba.conf
• Define what authentication method will be asked for :
• A user (role with the LOGIN attribute)
• Who wants to connect to a database
• From a particular host (or the local Unix Domain socket)
• Access is granted when :
• A line matches
• AND the method is NOT reject
• AND the client correctly answer to authentication method
• Superusers cannot bypass this check
• The pg_hba.conf file is walked from top to bottom, the server stops when a line matches or at the bottom

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] 
     | ALL [ PRIVILEGES ] } 
    ON DATABASE database_name [, ...] 
    TO role_specification [, ...] [ WITH GRANT OPTION ]  

• CREATE: This allows somebody to create a schema inside the database. Note that a CREATE clause does not allow for the creation of tables;
• CONNECT: This allows somebody to connect to a database.

The main thing is that public is not a role in the sense that it can be dropped and renamed. We can simply see it as the equivalent of everybody on the system.

So, to ensure that not everybody can connect to any database at any time, CONNECT may have to be revoked from the general public. To do so, we can connect as a superuser and fix the problem:

REVOKE ALL ON DATABASE test FROM public;  

There are two choices here:

• We can allow the joe role directly so that only the joe role will be able to connect.
• Alternatively, we can grant permissions to the bookkeeper role. It is not risky if we grant permissions to the bookkeeper role, because the role is not allowed to log in to the instance in the first place, so it purely serves as a source of permissions.

ALTER DATABASE <db_name> owner to <new_owner>

SELECT   
  datname AS database_name,  
  pg_roles.rolname AS owner,  
  datacl AS privileges  -- access control list
FROM pg_database 
 JOIN   pg_roles 
ON pg_database.datdba = pg_roles.oid;

CREATE TABLE t_broken (id int);  # allowed for joe

Normal users will potentially spam public schema with all kinds of tables, and the entire setup might suffer. You should also keep in mind that if somebody is allowed to create an object, that person is also its owner. Ownership means that all permissions are automatically available to the creator, including the destruction of the object.

To take those permissions away from public, run the following line as a superuser:

test=# REVOKE ALL ON SCHEMA public FROM public;  

From now on, nobody can put things into your public schema without the correct permissions.

By default, it will try to put the table into one of the following schemas:

test=> SHOW search_path ; 
 search_path 
-----------------  
 "$user", public 

GRANT  { { CREATE | USAGE  } [, ...]  | ALL [ PRIVILEGES ] }  
  ON SCHEMA schema_name [, ...] 
TO role_specification [, ...]  [ WITH  GRANT  OPTION ] 

• CREATE means that somebody can put objects into a schema.
• USAGE means that somebody is allowed to enter the schema. Note that entering the schema does not mean that something inside the schema can actually be used; those permissions have not been defined yet. This just means that the user can see the system catalog for this schema.

Bez usage mozna byc wlascicielem tabel w cudzym schemacie lub miec uprawnienia do tabel i sie select-a nie wykona.

To allow the joe role to access the table it has created previously, the following line will be necessary (executed as a superuser):

test=# GRANT USAGE ON SCHEMA public TO bookkeeper;  

The joe role is now able to read its table as expected:

SELECT count(*) FROM t_broken; 

The joe role is also able to add and modify rows, because it happens to be the owner of the table.

test=> ALTER TABLE t_broken RENAME TO t_useful;  
ERROR:  permission denied for schema public 

Run the following line as a superuser:

GRANT CREATE ON SCHEMA public TO bookkeeper;  

CREATE permissions on the public schema are assigned to bookkeeper.

The joe role can now change the name of its table.

SELECT   
n.nspname AS schema_name, 
pg_roles.rolname AS owner,  
CASE  
WHEN has_schema_privilege(n.nspname, 'USAGE') THEN 'USAGE'  
WHEN has_schema_privilege(n.nspname, 'CREATE') THEN 'CREATE'  
WHEN has_schema_privilege(n.nspname, 'CREATE TEMPORARY TABLE') THEN 'CREATE TEMPORARY TABLE'  
WHEN has_schema_privilege(n.nspname, 'CREATE ON SCHEMA') THEN 'CREATE ON SCHEMA'  -- Add more WHEN clauses for other privileges as needed  
ELSE NULL END AS privileges,  n.nspacl AS acl  
FROM pg_namespace n  
JOIN pg_roles 
ON n.nspowner = pg_roles.oid;

The output {postgres=UC/postgres,test=UC/postgres} suggests that both the postgres and test roles have the USAGE and CREATE privileges on the schemas being inspected. The format of the output is an access control list (ACL) where UC stands for USAGE and CREATE.

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE 
              | REFERENCES | TRIGGER } 
    [, ...] | ALL [ PRIVILEGES ] } 
    ON { [ TABLE ] table_name [, ...] 
         | ALL TABLES IN SCHEMA schema_name [, ...] } 
    TO role_specification [, ...] [ WITH GRANT OPTION ] 

• SELECT: This allows you to read a table.
• INSERT: This allows you to add rows to the table (this also includes copy and so on; it is not only about the INSERT clause). Note that if you are allowed to insert, you are not automatically allowed to read. The SELECT and INSERT clauses are required in order to be able to read the data that you have inserted.
• UPDATE: This modifies the content of a table.
• DELETE: This is used to remove rows from a table.
• TRUNCATE: This allows you to use the TRUNCATE clause. Note that the DELETE and TRUNCATE clauses are two separate permissions because the 8 * TRUNCATE clause will lock the table, which is not done by the DELETE clause (not even if there is no WHERE condition).
• ``REFERENCES```: This allows the creation of foreign keys. It is necessary to have this privilege on both the referencing and referenced columns, otherwise, the creation of the key won't work.
• TRIGGER: This allows for the creation of triggers.

The nice thing about the GRANT clause is that we can set permissions on all tables in a schema at the same time. This greatly simplifies the process of adjusting permissions.

Use the WITH GRANT OPTION clause to ensure that normal users can pass on permissions to others, which has the advantage of being able to reduce the workload of the administrators quite significantly.

SELECT   table_name,  grantee,  privilege_type  
FROM  information_schema.table_privileges 
where table_name='table_name' ;

SELECT   specific_name,  grantee,  privilege_type  
FROM   information_schema.routine_privileges
where specific_name='routine_name' ;

GRANT  SELECT (id)  ON t_useful TO paul;  

If we are using column-level permissions, there is an important thing to keep in mind. We should stop using SELECT * as it will no longer work:

test=> SELECT * FROM t_useful; 
ERROR:  permission denied for relation t_useful

• https://www.cybertec-postgresql.com/en/postgresql-alter-default-privileges-permissions-explained/ ALTER DEFAULT PRIVILEGES clause to give users an option to make PostgreSQL automatically set the desired permissions as soon as an object comes into existence. It is now impossible to simply forget to set those rights.

postgres=# \h ALTER DEFAULT PRIVILEGES
Command: ALTER DEFAULT PRIVILEGES
Description: define default access privileges
Syntax:
ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
... 

Example usage - if the joe role creates a table, the paul role will automatically be able to use it.

ALTER DEFAULT PRIVILEGES FOR ROLE joe 
      IN SCHEMA public GRANT ALL ON TABLES TO paul;  

target_role - only changes the default privileges for objects created by target_role. Czyli defaultowe przywileje nadawane sa na obiekty dodawane tylko przez usera target_role. If you omit the FOR ROLE clause, the statement only affects objects created by the current user. Nie mozna wskazac jako target_rola ALL ROLES trzeba osobne polecenia dla kazdej roli tworzacej w schemacie obiekty. Przewaznie i tak jest to wlasciciel schematu.

Viewing default privileges changed by ALTER DEFAULT PRIVILEGES

SELECT defaclrole::regrole AS creator,
       defaclnamespace::regnamespace AS "schema",
       defaclobjtype AS object_type,
       defaclacl AS default_permissions

If you are using psql, you can use the command \ddp (“describe default privileges”)

It is difficult to DROP users](https://www.cybertec-postgresql.com/en/drop-role-or-drop-user-postgresql/) in PostgreSQL: you can only drop a role if it has no privileges or objects. This also applies to default privileges: before you can drop a role, you have to remove all altered default privileges for that role, as well as all default privileges that grant something to that role. To remove the two default privileges from the example above, you would have to

ALTER DEFAULT PRIVILEGES FOR ROLE laurenz
   IN SCHEMA laurenz REVOKE SELECT ON TABLES FROM duff;
 
ALTER DEFAULT PRIVILEGES FOR ROLE laurenz
   GRANT EXECUTE ON FUNCTIONS TO PUBLIC;

if your aim is to drop a role, you can remove all default privileges associated with that role (along with other privileges and owned objects) with DROP OWNED

DROP OWNED BY laurenz;

You need to be a member of the role or a superuser to execute that command.

ensure that everybody is only allowed to see their own transactions. Person A should not be allowed to see person B's data. In addition to that, it might also make sense that the boss of a division is allowed to see all the data in their part of the company.

postgres=# \h CREATE POLICY
Command: CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

URL: https://www.postgresql.org/docs/13/sql-createpolicy.html

\x
\z table 

user can only be removed if everything has been taken away from them.

test=# \h REASSIGN 
Command: REASSIGN OWNED
Description: change the ownership of database objects owned by a database role
Syntax:
REASSIGN OWNED BY { old_role | CURRENT_USER | SESSION_USER } [, ...]
               TO { new_role | CURRENT_USER | SESSION_USER }

REASSIGN OWNED  BY joe TO postgres;  
DROP ROLE joe;

The only way to make this more efficient is to make sure that as few permissions as possible are assigned to real people. Try to abstract as much as you can into roles, which, in turn, can be used by many people. If individual permissions are not assigned to real people, things tend to be easier in general.

# as superuser
grant replication_user to ebkadm  # ownerem tabeli byl replication user a chcemy zmienic na ebkadm
ALTER TABLE cas.params OWNER TO ebkadm;

# as ebkadm
grant all privileges on all tables in schema cas to replication_user;
grant all privileges on all sequences in schema cas to replication_user;

The access are allocated according to a hierarchical model : BD --> SCHEMA --> TABLES

1. Grant CONNECT to the database:

GRANT CONNECT ON DATABASE database_name TO username;

2. Grant USAGE on schema:

Need both schema USAGE rights and object rights to perform an action on an object, like SELECT from a table.

if you have rights to SELECT from a table, but not the right to see it in the schema that contains it then you cannot access the table. But an object on PUBLIC schema has a default GRANT of all rights to the role public, which every user/group is a member of, so a object in PUBLIC schema does not need a extra GRANT command, unless users have been revoked from PUBLIC schema access.

GRANT USAGE ON SCHEMA schema_name TO username;

3. Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

4. Grant all privileges on all tables in the schema:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

5. Grant all privileges on all sequences in the schema:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;

6. Grant all privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
GRANT all on SCHEMA ebk TO ebkadm;

7. Grant permission to create database:

ALTER USER username CREATEDB;

8. Make a user superuser:

ALTER USER myuser WITH SUPERUSER;

9. Remove superuser status:

ALTER USER username WITH NOSUPERUSER;

Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default. For example:

ALTER DEFAULT PRIVILEGES
FOR USER username
IN SCHEMA schema_name
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

For a production system, you can use this configuration :

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

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

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

ALTER TABLE/SEQUENCE objectname OWNER TO somerole;

SELECT 'ALTER TABLE \"'|| schemaname || '.' || tablename ||'\" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;


SELECT 'ALTER SEQUENCE \"'|| sequence_schema || '.' || sequence_name ||'\" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

SELECT 'ALTER VIEW \"'|| table_schema || '.' || table_name ||'\" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

-- Materialized View
SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

-- $NEWUSER is the postgresql new name of the new owner
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
            quote_ident(s.relname) || ' OWNER TO $NEWUSER')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E'pg\\_%' AND 
               nspname <> 'information_schema' AND 
               relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

ALTER DATABASE $OLD_OWNER TO $NEW_OWNER;
ALTER SCHEMA $OLD_OWNER TO $NEW_OWNER;

--If current owner is not postgres you can use this:
REASSIGN OWNED BY old_role [, ...] TO new_role
-- But if the current owner is postgres you definitely get error


    su - postgres
    psql
    REASSIGN OWNED BY [old_user] TO [new_user];
    \c [your database]
    REASSIGN OWNED BY [old_user] TO [new_user];

DO $$DECLARE r record;
DECLARE
    v_schema varchar := 'public';
    v_new_owner varchar := 'admin_ctes';
BEGIN
    FOR r IN 
        select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.tables where table_schema = v_schema
        union all
        select 'ALTER SEQUENCE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.sequences where sequence_schema = v_schema
        union all
        select 'ALTER VIEW "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.views where table_schema = v_schema
        union all
        select 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') OWNER TO ' || v_new_owner || ';' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schema
        union all
        select 'ALTER SCHEMA "' || v_schema || '" OWNER TO ' || v_new_owner 
        union all
        select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner 
    LOOP
        EXECUTE r.a;
    END LOOP;
END$$;

CREATE GROUP marketing WITH USER jonathan, david;
CREATE GROUP name;
ALTER GROUP name ADD USER uname1, ... ;
ALTER GROUP name DROP USER uname1, ... ;
DROP GROUP name;
SELECT groname FROM pg_group;

\dg # listing the existing groups

• Database roles are conceptually completely separate from operating system users.
• Database roles are global across a database cluster installation (and not per individual database).
• Instance always contains one predefined role “superuser” - by default have the same name as the operating system user that initialized the database cluster
• Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.

CREATE ROLE name;
DROP ROLE name;
SELECT rolname FROM pg_roles;

\du  #  listing the existing roles

When a new database is created, PostgreSQL by default creates a schema named public and grants access on this schema to a backend role named public. All new users and roles are by default granted this public role, and therefore can create objects in the public schema.

• Revoke privileges from 'public' role

--the permissions inherited via the public role allow the user to create objects in the public schema
REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- revoke the default create permission on the public schema from the public role
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC; --  revokes the public role’s ability to connect to the database
--!!! Revoking permissions from the public role impacts all existing users and roles. !!!

• Read-only role

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydatabase TO readonly;
GRANT USAGE ON SCHEMA myschema TO readonly;   -- grants SELECT access to the readonly role on all the existing tables and views in the schema myschema but not to new created
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly; -- to ensure that new tables and views are also accessible

• Read/write role

CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydatabase TO readwrite;
GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;

• Creating database users

CREATE USER myuser1 WITH PASSWORD 'secret_passwd';
GRANT readonly TO myuser1; -- GRANT readwrite TO db_user;

• Checking the granted roles

SELECT 
      r.rolname, 
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;

SELECT 
      r.rolname, 
      r.rolsuper, 
      r.rolinherit,
      r.rolcreaterole,
      r.rolcreatedb,
      r.rolcanlogin,
      r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

1. Set a password for postgres:

ALTER ROLE postgres WITH PASSWORD 'new_password';

2. Configure pg_hba.conf to use the md5 method and reload
3. Give ownership of databases to a non applicative role
4. Revoke rights from the PUBLIC role:

REVOKE ALL ON DATABASE db_name FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

Then one can:

• Grant rights to applicative roles
• Setup default privileges to ease the management of rights

• The owner of an object can:
  • Access and modify its contents
  • Modify its structure
  • Drop it provided it has the right to modify the parent object
• Someone who does not own an object:
  • Cannot access/modify the contents unless a right is granted
  • Cannot modify it definition (no rights exists for that) !!!!
  • Can drop it if he/she owns the schema !!!!

• Views:
  • Rights needed to access them like any other relation
  • The underlying query is executed with the rights of their owner
• Functions:
  • Rights needed to execute them
  • Can be executed with the priviliges of their owner (SECURITY DEFINER)

• *acl columms in tables of the system catalog, mainly:
  • pg_database: datacl ⇒ Database rights (\l)
  • pg_namespace: nspacl ⇒ Schema rights (\dn+)
  • pg_class: relacl ⇒ Tables, Views and Sequences (\dp)
  • pg_proc: proacl ⇒ Functions
• If empty, then default rights
• Format documented on the documentation of GRANT

A way to automatically give rights at object creation

ALTER DEFAULT PRIVILEGES FOR role IN SCHEMA nsp GRANT right ON objects TO other_role

When role:

• creates an object of the “objects” (table, sequence...)
• inside the nsp schema
• then right is automatically granted to other_role on the new object
• use \ddp in psql to view default privileges

CREATE ROLE readonly LOGIN PASSWORD 'some_pass';
-- Existing objects
GRANT CONNECT ON DATABASE the_db TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
-- New objects
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

• The best is to use different roles for managing the structure and the content:
  • The owner takes care of the structure
  • The owner has default privileges to let an application role modify the data
• Default privileges can be used to clean rights before going to production:
  • Setup the default privileges
  • Restore dumps with pg_restore -U ddl_user -X -o

• Allow to enhance security by asking SELinux if access can by granted to an object
• SELinux context is checked after regular privileges (like on the system)
• Can enforce the external policy up to the column (like regular privileges)

Prerequisites

• A SELinux enabled system, e.g. Linux only
• PostgreSQL >= 9.1
• The sepgsql module (–with-selinux)
• The Reference Policy module for PostgreSQL loaded
• IPSec or some way to label what comes from the network
• Knowlegde on SELinux policy development

• Confine the PostgreSQL server on the Linux side:
  • load the postgresql.pp SELinux Policy module
  • (re)label the files of the PostgreSQL installation
• Load sepgsql at the cluster startup:

    shared_preload_libraries = 'sepgsql'

• Create the SE-PostgreSQL functions inside the database:

    \i /path/to/contrib/sepgsql.sql;
    SELECT sepgsql-restorecon(NULL);

• The reference policy gives some interfaces for SELinux roles (see postgresql.if)
• The reference policy gives examples on possible rights
• Use SECURITY LABEL statements to label the objects

With SE-PostgreSQL in 9.1:

• No labels for database
• No row level labels
• No Data Definition Language rights
• Unable to hide object existence, only the contents

• https://www.slideshare.net/jkatz05/get-your-insecure-postgresql-passwords-to-scram

Postgres 10 SCRAM-SHA-256 Postgres 11 SCRAM-SHA-256-PLUS

db=> SELECT * FROM users;
 id │ username │ personal_id │   password_hash
────┼──────────┼─────────────┼───────────────────
  1 │ haki     │ 12222227    │ super-secret-hash

db=> GRANT SELECT (id, username) ON users TO analyst;  -- grant aces only to id,username columns

db=> SELECT * FROM users;
ERROR:  permission denied for table users

db=> SELECT id, username, personal_id FROM users;
ERROR:  permission denied for table users

db=> SELECT id, username FROM users;
 id │ username
────┼──────────
  1 │ haki