• Ryan Booz
• FOSDEM Postgres Devroom 2024
The Building Blocks
Roles
Special Roles
Privileges
Inheritance
Object Ownership
Predefined Roles
Cluster Cluster Cluster
Server/Host (Firewall, Ports)
Port: 5432
pg_hba.conf
Port: 5433
pg_hba.conf
Port: 5434
pg_hba.conf
ROLES
Cluster
Databases
Database
Cluster
ROLE
• First layer of authentication
• Similar to a firewall ruleset for PostgreSQL
• Cloud vendors largely manage this for you
Which hosts & roles, can connect to what databases,
using what authentication method?
# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
# The same using local loopback TCP/IP connections.
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 trust
# Allow any user from host 192.168.12.10 to connect to database
# "postgres" if the user's password is correctly supplied.
#
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.12.10/32 scram-sha-256
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
• Own databases, schemas, and objects
• Tables, Functions, Views, Etc.
• Have cluster-level privileges (attributes)
• Granted privileges to databases, schemas, and
objects
• Can possibly grant privileges to other roles
• Semantically the same as roles
• By Convention:
• User = LOGIN
• Group = NOLOGIN
• PostgreSQL 8.2+ CREATE (USER|GROUP) is an
alias
CREATE ROLE user1 WITH LOGIN PASSWORD 'abc123' INHERIT;
CREATE USER user1 WITH PASSWORD 'abc123' INHERIT;
CREATE GROUP group1 WITH INHERIT;
• Predefined settings that can be enabled/disabled
for a given role
• Essentially cluster-level (non-database) privileges
• Map to columns in pg_catalog.pg_roles
LOGIN
SUPERUSER
CREATEROLE
CREATEDB
REPLICATION LOGIN
PASSWORD
INHERIT
BYPASSRLS
CONNECTION LIMIT
• Roles can set role-specific defaults for run-time
configuration at connection time
• Any settings that can be set via SET command can
be altered for a ROLE
ALTER ROLE user1 SET jit TO off;
ALTER ROLE user1 RESET jit;
• is created by default when the cluster is initialized
• Typically named postgres because the system
process user initiates a initdb
• Bypasses all security checks except LOGIN
• Full privilege to do "anything"
• Treat superuser with care (like root on Linux)
• Create a role with the right level of control
• Recommend adding CREATEROLE and CREATEDB
• Allows user management and database ownership
• May still limit some actions (e.g. installing
extensions limited to superuser)
• The set of access rights to databases, schemas,
and objects
• Can be granted (GRANT) or revoked (REVOKE) by a
role with authority
• Explicit GRANT or REVOKE only impacts existing
objects
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE
SET
ALTER SYSTEM
• All roles are granted implicit membership to PUBLIC
• The public role cannot be deleted
• Granted CONNECT, USAGE, TEMPORARY, and EXECUTE
by default
• <=PG14: CREATE on the public schema by default
• >=PG15: No CREATE on public schema by default
• Revoke all privileges on the public schema from the
PUBLIC role
• Revoke all database privileges from the PUBLIC
role (maybe)
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE db_name FROM PUBLIC;
-- grant the ability to create a schema
GRANT CREATE ON DATABASE app_db TO admin1;
-- see and create objects in schema
GRANT USAGE,CREATE IN SCHEMA demo_app TO dev1;
-- allow some roles only some privileges
GRANT SELECT,INSERT,UPDATE
ON ALL TABLES IN SCHEMA demo_app TO jr_dev;
• Remember, explicit grants only effect existing
database objects!
-- This will only grant to existing objects
GRANT ALL TO ALL TABLES IN SCHEMA public TO dev1;
• Roles can be granted membership into another role
• If a role has INHERIT set, they automatically have
usage of privileges from member roles
• The preferred method for managing group privileges
CREATE ROLE sr_dev WITH LOGIN password='abc' INHERIT;
CREATE ROLE rptusr WITH LOGIN password='123' INHERIT;
CREATE ROLE admin WITH NOLOGIN NOINHERIT;
CREATE ROLE ropriv WITH NOLOGIN NOINHERIT;
GRANT INSERT,UPDATE,DELETE ON ALL TABLES
IN SCHEMA app TO admin;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO ropriv;
GRANT admin,ropriv TO sr_dev;
GRANT ropriv TO rptusr;
ropriv
sr_dev
rptusr
SELECT SELECT
ropriv
admin
sr_dev
rptusr
SELECT SELECT, INSERT,
UPDATE, DELETE
• Object creator = owner
• Owner is a "superuser" of the objects they own
• Initial object access = Principle of Least Privilege
• Unless specifically granted ahead of time, objects are owned
and "accessible" by the creator/superuser only
• Roles can specify default privileges to GRANT for each
object type that they create
Database
Cluster
ROLE
Database
Cluster
ROLE
ALTER DEFAULT PRIVILEGES
GRANT SELECT ON TABLES TO public;
cituscon=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+-------+---------------------------
postgres | | table | =r/postgres +
| | | postgres=arwdDxt/postgres
Option 1: (owner)
Explicitly GRANT access
after object creation
Option 4: (PG14+)
Use pg_read_all_data
or pg_write_all_data
predefined roles
Option 2: (owner)
ALTER DEFAULT
PRIVILEGES
Option 3:
SET ROLE to app role
before creation with
correct default privileges
• CREATE OR REPLACE doesn't change ownership
• Security issue with users that have create
permissions (particularly the public schema)
• PostgreSQL 15 removes default create permissions
from PUBLIC on the public schema
• Cluster-level roles that can be granted
• Work starting in PostgreSQL 14+ to simplify
privilege management
• pg_read_all_data (for example)
• If a role that has CONNECT to a database, they can SELECT
from all tables
