PostgreSQL User & Role Management
Table of Contents
1. Users vs. Roles: There’s No Difference
The primary difference between CREATE ROLE and CREATE USER is that CREATE USER automatically includes the LOGIN privilege, allowing the user to connect to the database, whereas CREATE ROLE does not grant the LOGIN privilege unless it is explicitly specified.
PostgreSQL internally treats users and groups as roles. Therefore, using CREATE ROLE is often recommended. However, CREATE USER and CREATE GROUP are still supported and commonly used by DBAs. You can use whichever command you find easier to understand and manage.
Both users and roles are stored in the same PostgreSQL system catalog (pg_authid / pg_roles). However, the pg_user view displays only users & roles with the LOGIN privilege, not all roles.
postgres=# CREATE USER rama;
CREATE ROLE
postgres=# SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'rama';
rolname | rolcanlogin
---------+-------------
rama | t <--------- CREATE USER auto-sets rolcanlogin = true
(1 row)
postgres=#
postgres=# CREATE ROLE ravi;
CREATE ROLE
postgres=# SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname = 'ravi';
rolname | rolcanlogin
---------+-------------
ravi | f <---------- CREATE ROLE leaves rolcanlogin = false
(1 row)
postgres=#
postgres-# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
rama |
ravi | Cannot login
postgres-#
2. Creating Users and Roles
-- Login accounts
postgres=# CREATE USER raj WITH PASSWORD 'raj_pwd123';
CREATE ROLE
postgres=# CREATE USER sugi WITH PASSWORD 'sugi_pwd123';
CREATE ROLE
postgres=# CREATE USER teja WITH PASSWORD 'teja_pwd123';
CREATE ROLE
postgres=# CREATE USER gebadm WITH PASSWORD 'gebadm_pwd123'; <-- we will set as schema owner
CREATE ROLE
postgres=# CREATE USER gebusr WITH PASSWORD 'gebusr_pwd123'; <-- app read-only user
CREATE ROLE
postgres=#
-- NOLOGIN group roles (permission bundles)
postgres=# CREATE ROLE geb_ro NOLOGIN; <-- read-only privilege bundle
CREATE ROLE
postgres=# CREATE ROLE geb_rw NOLOGIN; <-- read-write privilege bundle
CREATE ROLE
postgres=#
postgres=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
geb_ro | Cannot login
geb_rw | Cannot login
gebadm |
gebusr |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
raj |
rama |
ravi | Cannot login
sugi |
teja |
postgres=#
Both users and roles are stored in the same PostgreSQL system catalog (pg_authid / pg_roles). However, the pg_user view displays only users & roles with the LOGIN privilege, not all roles.
postgres=# select rolname from pg_roles;
rolname
-----------------------------
pg_database_owner
pg_read_all_data
pg_write_all_data
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
pg_checkpoint
pg_maintain
pg_use_reserved_connections
pg_create_subscription
postgres
rama
ravi
raj
sugi
teja
gebadm
gebusr
geb_ro
geb_rw
(25 rows)
postgres=#
postgres=# select usename from pg_user;
usename
----------
postgres
rama
raj
sugi
teja
gebadm
gebusr
(7 rows)
postgres=#Roles like geb_ro and geb_rw never log in directly. Instead, real users get added to them with GRANT role TO user, which is far easier to manage than granting privileges to each person individually.
3. Databases, Schemas, and Ownership
Once the roles exist, you can create a database and schema owned by the appropriate role, then open the “door” to that schema for everyone who needs it.
postgres=# CREATE DATABASE gebua;
CREATE DATABASE
postgres=#
postgres=# \c gebua postgres
You are now connected to database "gebua" as user "postgres".
gebua=#
gebua=# CREATE SCHEMA geb AUTHORIZATION gebadm;
CREATE SCHEMA
gebua=# GRANT USAGE ON SCHEMA geb TO geb_ro, geb_rw, raj, sugi, teja;
GRANT
gebua=#
gebua=# \l gebua
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-------+----------+----------+-----------------+-------------+-------------+--------+-----------+-------------------
gebua | postgres | UTF8 | libc | en_SG.UTF-8 | en_SG.UTF-8 | | |
(1 row)
gebua=#
gebua=# \dn
List of schemas
Name | Owner
--------+-------------------
geb | gebadm
public | pg_database_owner
(2 rows)
gebua=#
gebua=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
geb | gebadm | gebadm=UC/gebadm +|
| | geb_ro=U/gebadm +|
| | geb_rw=U/gebadm +|
| | raj=U/gebadm +|
| | sugi=U/gebadm +|
| | teja=U/gebadm |
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
(2 rows)
gebua=#
U = USAGE privilege <---- USAGE means the user can see that tables and other objects exist in the schema, but cannot view or modify their data unless additional permissions are granted.
C = CREATE privilege
postgres=# \c gebua gebadm
You are now connected to database "gebua" as user "gebadm".
gebua=>
gebua=> CREATE TABLE geb.dept ( dept_id SERIAL PRIMARY KEY, dept_name VARCHAR(100) NOT NULL);
CREATE TABLE
gebua=> CREATE TABLE geb.emp (emp_id SERIAL PRIMARY KEY, emp_name VARCHAR(100) NOT NULL,dept_id INT REFERENCES geb.dept(dept_id),salary NUMERIC(10,2));
CREATE TABLE
gebua=>
gebua=> \dt geb.*
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
geb | dept | table | gebadm
geb | emp | table | gebadm
(2 rows)
gebua=>
gebua=> INSERT INTO geb.dept (dept_name) VALUES ('Engineering'), ('Sales'), ('HR');
INSERT 0 3
gebua=> INSERT INTO geb.emp (emp_name, dept_id, salary) VALUES ('Arjun', 1, 75000),('Meena', 2, 65000),('Kabir', 3, 60000);
INSERT 0 3
gebua=>
gebua=> select * from emp;
ERROR: relation "emp" does not exist
LINE 1: select * from emp;
^
gebua=> select * from geb.emp;
emp_id | emp_name | dept_id | salary
--------+----------+---------+----------
1 | Arjun | 1 | 75000.00
2 | Meena | 2 | 65000.00
3 | Kabir | 3 | 60000.00
(3 rows)
gebua=> select * from geb.dept;
dept_id | dept_name
---------+-------------
1 | Engineering
2 | Sales
3 | HR
(3 rows)
gebua=>
4. Granting Privileges on Existing Objects
GRANT SELECT ON ALL TABLES IN SCHEMA geb TO geb_ro; <------Will automatically give SELECT access to future tables. It does not.
This command grants permissions only to the tables that exist at the time you run it. Any tables created later will not receive these permissions automatically. To ensure that new tables also get the required permissions, you must use ALTER DEFAULT PRIVILEGES.
-- Grant privilleges to RO (Read-only) role.
[postgres@pgdb02 ~]$ psql
psql (17.10)
Type "help" for help.
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA geb TO geb_ro;
ERROR: schema "geb" does not exist
postgres=#
postgres=# \c gebua
You are now connected to database "gebua" as user "postgres".
gebua=#
gebua=# GRANT SELECT ON ALL TABLES IN SCHEMA geb TO geb_ro;
GRANT
gebua=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA geb TO geb_ro;
GRANT
gebua=#
-- Grant privilleges to RW (Read-Write) role.
gebua=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA geb TO geb_rw;
GRANT
gebua=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA geb TO geb_rw;
GRANT
gebua=#
-- Assign RO role to user
gebua=# GRANT geb_ro TO gebusr;
GRANT ROLE
gebua=#
gebua=# \dp geb.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------------------+----------+------------------------+-------------------+----------
geb | dept | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm | |
geb | dept_dept_id_seq | sequence | gebadm=rwU/gebadm +| |
| | | geb_ro=rU/gebadm +| |
| | | geb_rw=rU/gebadm | |
geb | emp | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm | |
geb | emp_emp_id_seq | sequence | gebadm=rwU/gebadm +| |
| | | geb_ro=rU/gebadm +| |
| | | geb_rw=rU/gebadm | |
(4 rows)
gebua=#
| Code | Meaning |
| ---- | ---------- |
| r | SELECT |
| a | INSERT |
| w | UPDATE |
| d | DELETE |
| D | TRUNCATE |
| x | REFERENCES |
| t | TRIGGER |
| m | MAINTAIN | <------
What is m (MAINTAIN)?
The MAINTAIN privilege allows operations such as:
VACUUM
ANALYZE
REINDEX
CLUSTER
REFRESH MATERIALIZED VIEW
LOCK TABLE
-- Verify
[postgres@pgdb02 ~]$ psql -d gebua -U gebusr
psql (17.10)
Type "help" for help.
gebua=> \c
You are now connected to database "gebua" as user "gebusr".
gebua=>
gebua=> \dt geb.*
List of relations
Schema | Name | Type | Owner
--------+------+-------+--------
geb | dept | table | gebadm
geb | emp | table | gebadm
(2 rows)
gebua=> select * from geb.emp;
emp_id | emp_name | dept_id | salary
--------+----------+---------+----------
1 | Arjun | 1 | 75000.00
2 | Meena | 2 | 65000.00
3 | Kabir | 3 | 60000.00
(3 rows)
gebua=> select * from geb.dept;
dept_id | dept_name
---------+-------------
1 | Engineering
2 | Sales
3 | HR
(3 rows)
gebua=>5. Default Privileges for Future Objects
ALTER DEFAULT PRIVILEGES allows you to define permissions that will be automatically granted to future objects created by a specific role.
In this example, whenever gebadm creates a new table in the geb schema:
geb_roautomatically receivesSELECTpermission (read-only access).geb_rwautomatically receivesSELECT,INSERT,UPDATE, andDELETEpermissions (read-write access).
-- For RO Role
gebua=# \c
You are now connected to database "gebua" as user "postgres".
gebua=# ALTER DEFAULT PRIVILEGES FOR ROLE gebadm IN SCHEMA geb GRANT SELECT ON TABLES TO geb_ro;
ALTER DEFAULT PRIVILEGES
gebua=# ALTER DEFAULT PRIVILEGES FOR ROLE gebadm IN SCHEMA geb GRANT USAGE, SELECT ON SEQUENCES TO geb_ro;
ALTER DEFAULT PRIVILEGES
gebua=#
-- For RW Role
gebua=# ALTER DEFAULT PRIVILEGES FOR ROLE gebadm IN SCHEMA geb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO geb_rw;
ALTER DEFAULT PRIVILEGES
gebua=# ALTER DEFAULT PRIVILEGES FOR ROLE gebadm IN SCHEMA geb GRANT USAGE, SELECT ON SEQUENCES TO geb_rw;
ALTER DEFAULT PRIVILEGES
gebua=#
-- Live proof: a brand-new table already has the right grants
[postgres@pgdb02 ~]$ psql -d gebua -U gebadm
psql (17.10)
Type "help" for help.
gebua=> \c
You are now connected to database "gebua" as user "gebadm".
gebua=>
gebua=> CREATE TABLE geb.payroll (payroll_id SERIAL PRIMARY KEY, amount NUMERIC);
CREATE TABLE
gebua=> INSERT INTO geb.payroll (amount) VALUES (50000.00);
INSERT 0 1
gebua=>
gebua=> \dp geb.payroll
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+------------------------+-------------------+----------
geb | payroll | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm | |
(1 row)
gebua=>
ALTER DEFAULT PRIVILEGES allows you to define permissions that will be automatically granted to future objects.
[postgres@pgdb02 ~]$ psql -d gebua -U gebusr
psql (17.10)
Type "help" for help.
gebua=> \c
You are now connected to database "gebua" as user "gebusr".
gebua=>
gebua=> select * from geb.payroll;
payroll_id | amount
------------+----------
1 | 50000.00 <------
(1 row)
gebua=>
6. Per-User, Per-Table Grants and Revokes
Sometimes you want a single user to see exactly one table, with no role membership involved:
postgres=# \c gebua
You are now connected to database "gebua" as user "postgres".
gebua=# GRANT SELECT ON geb.emp TO raj;
GRANT
gebua=# GRANT SELECT ON geb.dept TO sugi;
GRANT
gebua=# GRANT SELECT ON geb.payroll TO teja;
GRANT
gebua=#
-- Schema level
gebua=# \dn+ geb
List of schemas
Name | Owner | Access privileges | Description
------+--------+-------------------+-------------
geb | gebadm | gebadm=UC/gebadm +|
| | geb_ro=U/gebadm +|
| | geb_rw=U/gebadm +|
| | raj=U/gebadm +|
| | sugi=U/gebadm +|
| | teja=U/gebadm |
(1 row)
gebua=#
-- Table Level
gebua-# \dp geb.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------------------------+----------+------------------------+-------------------+----------
geb | dept | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm +| |
| | | sugi=r/gebadm | |
geb | dept_dept_id_seq | sequence | gebadm=rwU/gebadm +| |
| | | geb_ro=rU/gebadm +| |
| | | geb_rw=rU/gebadm | |
geb | emp | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm +| |
| | | raj=r/gebadm | |
geb | emp_emp_id_seq | sequence | gebadm=rwU/gebadm +| |
| | | geb_ro=rU/gebadm +| |
| | | geb_rw=rU/gebadm | |
geb | payroll | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm +| |
| | | teja=r/gebadm | |
geb | payroll_payroll_id_seq | sequence | gebadm=rwU/gebadm +| |
| | | geb_ro=rU/gebadm +| |
| | | geb_rw=rU/gebadm | |
(6 rows)
gebua-#
=== LIVE TEST: raj can read emp, but NOT dept/hr ===
gebua-# \c gebua raj
You are now connected to database "gebua" as user "raj".
gebua->
gebua=>
gebua=> SELECT * FROM geb.emp;
emp_id | emp_name | dept_id | salary
--------+----------+---------+----------
1 | Arjun | 1 | 75000.00
2 | Meena | 2 | 65000.00
3 | Kabir | 3 | 60000.00
(3 rows)
gebua=>
gebua=> SELECT * FROM geb.dept;
ERROR: permission denied for table dept
gebua=>
gebua=> SELECT * FROM geb.hr;
ERROR: relation "geb.hr" does not exist
LINE 1: SELECT * FROM geb.hr;
^
gebua=>
=== LIVE TEST: sugi can read dept, but NOT emp/hr ===
gebua=> \c gebua sugi
You are now connected to database "gebua" as user "sugi".
gebua=>
gebua=> SELECT * FROM geb.dept;
dept_id | dept_name
---------+-------------
1 | Engineering
2 | Sales
3 | HR
(3 rows)
gebua=> SELECT * FROM geb.emp;
ERROR: permission denied for table emp
gebua=>
gebua=> SELECT * FROM geb.hr;
ERROR: relation "geb.hr" does not exist
LINE 1: SELECT * FROM geb.hr;
^
gebua=>
=== LIVE TEST: teja can read hr, but NOT emp/dept ===
gebua=> \c gebua teja
You are now connected to database "gebua" as user "teja".
gebua=>
gebua=> SELECT * FROM geb.payroll;
payroll_id | amount
------------+----------
1 | 50000.00
(1 row)
gebua=> SELECT * FROM geb.emp;
ERROR: permission denied for table emp
gebua=>
gebua=> SELECT * FROM geb.dept;
ERROR: permission denied for table dept
gebua=>
Each of these users has no membership in geb_ro or geb_rw — they get exactly the one table explicitly granted, nothing more. Taking access away later uses REVOKE.
--- Revoke
[postgres@pgdb02 ~]$ psql
psql (17.10)
Type "help" for help.
postgres=# \c gebua
You are now connected to database "gebua" as user "postgres".
gebua=# REVOKE SELECT ON geb.emp FROM raj;
REVOKE
gebua=# REVOKE SELECT ON geb.dept FROM sugi;
REVOKE
gebua=# REVOKE SELECT ON geb.payroll FROM teja;
REVOKE
gebua=#
gebua=# \dp geb.emp;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+------------------------+-------------------+----------
geb | emp | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm | |
(1 row)
gebua=# \dp geb.dept;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+------------------------+-------------------+----------
geb | dept | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm | |
(1 row)
gebua=# \dp geb.payroll;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+------------------------+-------------------+----------
geb | payroll | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm | |
(1 row)
gebua=#
gebua=# \c gebua raj
You are now connected to database "gebua" as user "raj".
gebua=> select * from geb.emp;
ERROR: permission denied for table emp
gebua=>
gebua=> \c gebua sugi;
You are now connected to database "gebua" as user "sugi".
gebua=>
gebua=> select * from geb.dept;
ERROR: permission denied for table dept
gebua=>
gebua=> \c gebua teja
You are now connected to database "gebua" as user "teja".
gebua=>
gebua=> select * from geb.payroll;
ERROR: permission denied for table payroll
gebua=>
-- Re GRANT
gebua=# \c
You are now connected to database "gebua" as user "postgres".
gebua=# GRANT SELECT ON geb.emp TO raj;
GRANT
gebua=# GRANT SELECT ON geb.dept TO sugi;
GRANT
gebua=# GRANT SELECT ON geb.payroll TO teja;
GRANT
gebua=#
You can confirm what’s actually in effect with \dn+ geb (schema-level owner and ACL) and \dp geb.* (table-level ACL for every object).
7. The PUBLIC Pseudo-Role
Every role you create — including ones that don’t exist yet — is automatically and silently a member of PUBLIC. You never grant membership to it; it’s implicit and universal. Anything granted “TO PUBLIC” is inherited by every current and future user.
Fresh databases ship with a few defaults granted to PUBLIC:
- CONNECT and TEMP on every new database
- EXECUTE on every newly created function or procedure
- USAGE + CREATE on the
publicschema (PG < 15 only — PG15+ removed CREATE from PUBLIC by default)
gebua=# \c gebua raj
You are now connected to database “gebua” as user “raj”.
gebua=> create table test (id init);
ERROR: permission denied for schema public
LINE 1: create table test (id init);
^
gebua=>
gebua=> \c gebua postgres
You are now connected to database "gebua" as user "postgres".
gebua=# REVOKE ALL ON DATABASE gebua FROM PUBLIC;
REVOKE
gebua=# REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
gebua=#
gebua=# \c gebua raj
connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: permission denied for database "gebua"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
gebua=#
gebua=# GRANT CONNECT ON DATABASE gebua TO raj, sugi, teja, gebadm, gebusr;
GRANT
gebua=#
gebua=# \c gebua raj
You are now connected to database "gebua" as user "raj".
gebua=>
8. GRANT ALL
GRANT ALL is broader than it sounds. On a table, it grants every applicable privilege — SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER — not just the common four.
The extra three matter:
- TRUNCATE — can wipe the entire table instantly
- REFERENCES — can create a foreign key pointing at this table
- TRIGGER — can create triggers on this table
gebua=# CREATE USER anu WITH PASSWORD 'anu';
CREATE ROLE
gebua=# GRANT CONNECT ON DATABASE GEBUA TO ANU;
GRANT
gebua=# GRANT ALL PRIVILEGES ON GEB.EMP TO ANU;
GRANT
gebua=#
gebua=# \dp geb.emp;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+------------------------+-------------------+----------
geb | emp | table | gebadm=arwdDxtm/gebadm+| |
| | | geb_ro=r/gebadm +| |
| | | geb_rw=arwd/gebadm +| |
| | | raj=r/gebadm +| |
| | | anu=arwdDxtm/gebadm | |
(1 row)
gebua=#
9. Inspecting Roles: pg_roles, pg_user, pg_shadow, pg_group
PostgreSQL exposes several system catalogs for auditing roles, each with a different scope:
- pg_roles — all roles, both login users and NOLOGIN roles.
- pg_user — a view filtered from pg_roles showing only roles where
rolcanlogin = true. Roles likegeb_ro/geb_rwnever appear here. - pg_shadow — password hashes (SCRAM/MD5, never plaintext), visible only to superusers or for your own row.
- pg_group — a legacy compatibility view predating the unified role system; it only lists NOLOGIN roles and derives its data live from
pg_auth_members.
For a complete, modern view of role memberships — including login roles that are members of other roles — query pg_auth_members directly:
gebua=# select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_database_owner | f | t | f | f | f | f | -1 | ******** | | f | | 6171
pg_read_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6181
pg_write_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6182
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
pg_checkpoint | f | t | f | f | f | f | -1 | ******** | | f | | 4544
pg_maintain | f | t | f | f | f | f | -1 | ******** | | f | | 6337
pg_use_reserved_connections | f | t | f | f | f | f | -1 | ******** | | f | | 4550
pg_create_subscription | f | t | f | f | f | f | -1 | ******** | | f | | 6304
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
rama | f | t | f | f | t | f | -1 | ******** | | f | | 41527
ravi | f | t | f | f | f | f | -1 | ******** | | f | | 41528
raj | f | t | f | f | t | f | -1 | ******** | | f | | 41531
sugi | f | t | f | f | t | f | -1 | ******** | | f | | 41532
teja | f | t | f | f | t | f | -1 | ******** | | f | | 41533
gebadm | f | t | f | f | t | f | -1 | ******** | | f | | 41534
gebusr | f | t | f | f | t | f | -1 | ******** | | f | | 41535
geb_ro | f | t | f | f | f | f | -1 | ******** | | f | | 41536
geb_rw | f | t | f | f | f | f | -1 | ******** | | f | | 41537
anu | f | t | f | f | t | f | -1 | ******** | | f | | 41572
(26 rows)
gebua=#
gebua=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
rama | 41527 | f | f | f | f | ******** | |
raj | 41531 | f | f | f | f | ******** | |
sugi | 41532 | f | f | f | f | ******** | |
teja | 41533 | f | f | f | f | ******** | |
gebadm | 41534 | f | f | f | f | ******** | |
gebusr | 41535 | f | f | f | f | ******** | |
anu | 41572 | f | f | f | f | ******** | |
(8 rows)
gebua=#
gebua=# select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------------------+----------+-----------
postgres | 10 | t | t | t | t | SCRAM-SHA-256$4096:IXXmstZ3yAPBDYk9SaYwoQ==$XERgxvz+WfnEzw0FO5wdHWgkZDNXGTJwh+9ku0fAHUE=:i9zQsVpTsUxxBpe3Rs08ValuokiqjnrNHjtk9eKDT8A= | |
rama | 41527 | f | f | f | f | | |
raj | 41531 | f | f | f | f | SCRAM-SHA-256$4096:83y0vE12G/RH9B6pxOx9FA==$lYuVyvhTsvaGvjUXqWBir2F34gZLq7C7fW3zg8L6jZA=:R1PzeM8NTQjT0kHUASeSgnSXhFWiX5dcTwd9fNiy7Lg= | |
sugi | 41532 | f | f | f | f | SCRAM-SHA-256$4096:obX6SJ88dSoqJDh9YgKQ6Q==$sH5xVe6Li+95vTluTyell1y/IUIVbip9nUINWkQirQk=:SpJjKrDCnv3LNvwZBzoh6nY23D9fCCtKNYoMl9AKpKg= | |
teja | 41533 | f | f | f | f | SCRAM-SHA-256$4096:VD9NCq0s1UbQv/ucdEtOMg==$FaWKK/ovAC45oxgOfGKmTqNQqvavQaPdKRUGEgAi7Rc=:EQwYGJhvD89OGrkqVrOWr27I0OftzNgdO7kiZgGnaPM= | |
gebadm | 41534 | f | f | f | f | SCRAM-SHA-256$4096:Xo6mPeiuPXBaIqN6tFbH7Q==$WG4m8yARZ9ZV40H7HgZVkG5TrvS83HgSFj59Uc1GlWo=:p/1zT72iOnajg7dwwo9xjmD2wRIuOugtr/0lJWXA6t0= | |
gebusr | 41535 | f | f | f | f | SCRAM-SHA-256$4096:mvnPDdeNDh+olgPT6d05Yg==$19cDwk3Yy/hQRC8SmGcgHkwoHT86YFkYHAsdRTatzyQ=:CZupkFOFhqRIyK4LUhg7OcQZFpzpKB8VKdzNZphrkrQ= | |
anu | 41572 | f | f | f | f | SCRAM-SHA-256$4096:mXGdfJgtLi3531WT+4S93g==$wlYQ1SLzh0aNcESBEQ7jVgLkQqr4F6d674xQsNpSo1U=:WRudEkJcQvuHqcOpTgqWf6wQlizqPcS9MJ6lyT2AsyI= | |
(8 rows)
gebua=# select * from pg_authid;
gebua=# SELECT * FROM pg_ident_file_mappings;
map_number | file_name | line_number | map_name | sys_name | pg_username | error
------------+-----------+-------------+----------+----------+-------------+-------
(0 rows)
gebua=#
gebua=# select * from pg_group;
groname | grosysid | grolist
-----------------------------+----------+---------
pg_database_owner | 6171 | {}
pg_read_all_data | 6181 | {}
pg_write_all_data | 6182 | {}
pg_monitor | 3373 | {}
pg_read_all_settings | 3374 | {3373}
pg_read_all_stats | 3375 | {3373}
pg_stat_scan_tables | 3377 | {3373}
pg_read_server_files | 4569 | {}
pg_write_server_files | 4570 | {}
pg_execute_server_program | 4571 | {}
pg_signal_backend | 4200 | {}
pg_checkpoint | 4544 | {}
pg_maintain | 6337 | {}
pg_use_reserved_connections | 4550 | {}
pg_create_subscription | 6304 | {}
ravi | 41528 | {}
geb_ro | 41536 | {41535}
geb_rw | 41537 | {}
(18 rows)
gebua=#
-- Granted Roles
gebua=# SELECT r.rolname AS member, g.rolname AS granted_role FROM pg_auth_members m JOIN pg_roles r ON m.member = r.oid JOIN pg_roles g ON m.roleid = g.oid ORDER BY g.rolname, r.rolname;
member | granted_role
------------+----------------------
gebusr | geb_ro
pg_monitor | pg_read_all_settings
pg_monitor | pg_read_all_stats
pg_monitor | pg_stat_scan_tables
(4 rows)
gebua=#
One more distinction worth clearing up: pg_ident.conf is not a password catalog at all. It’s a config file mapping OS-level usernames to Postgres role names, used only for ident/peer/GSSAPI/cert authentication. It contains zero passwords — pg_shadow answers “is this the right password for this role?”, while pg_ident answers “which Postgres role does this externally-authenticated identity map to?”
10. Password Management
PostgreSQL has no native equivalent to “force password change on first login.” The closest built-in tool is VALID UNTIL, which can only block login after a timestamp — it can’t force a change flow. Common workarounds:
- Set VALID UNTIL to a timestamp already in the past, so the account is “pre-expired” and a DBA must reset it before the user’s first login.
- Track a
must_change_passwordflag in your own application table and enforce it in app logic. - Use community extensions like passwordcheck or credcheck for complexity/expiry policy enforcement.
gebua=# show password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)
gebua=#
[postgres@pgdb02 data]$ PGPASSWORD='raj_pwd123' psql -h 192.168.2.23 -d gebua -U raj
psql (17.10)
Type "help" for help.
gebua=> \c
You are now connected to database "gebua" as user "raj".
gebua=>
-- Simulate a "pre-expired" account
gebua=# ALTER USER raj VALID UNTIL '2020-01-01';
ALTER ROLE
gebua=# SELECT usename, valuntil FROM pg_user WHERE usename = 'raj';
usename | valuntil
---------+------------------------
raj | 2020-01-01 00:00:00+08 <------------
(1 row)
gebua=# \du raj
List of roles
Role name | Attributes
-----------+---------------------------------------------
raj | Password valid until 2020-01-01 00:00:00+08
gebua=#
[postgres@pgdb02 data]$ cat pg_hba.conf | grep -i raj
host gebua raj 0.0.0.0/0 md5
[postgres@pgdb02 data]$
[postgres@pgdb02 data]$ pg_ctl reload -D /pgData/pgsql17/data/
server signaled
[postgres@pgdb02 data]$
[postgres@pgdb02 data]$ PGPASSWORD='raj_pwd123' psql -h 192.168.2.23 -d gebua -U raj
psql: error: connection to server at "192.168.2.23", port 5432 failed: FATAL: password authentication failed for user "raj"
[postgres@pgdb02 data]$
-- Extend validity again later
ALTER USER raj VALID UNTIL '2027-08-06'; ---- OR ------ ALTER USER raj VALID UNTIL 'infinity';
postgres=# ALTER USER raj VALID UNTIL 'infinity';
ALTER ROLE
postgres=# exit
[postgres@pgdb02 data]$ PGPASSWORD='raj_pwd123' psql -h 192.168.2.23 -d gebua -U raj
psql (17.10)
Type "help" for help.
gebua=> \c
You are now connected to database "gebua" as user "raj".
gebua=>
postgres=# \du raj
List of roles
Role name | Attributes
-----------+-------------------------------
raj | Password valid until infinity
postgres=#
--- Restore the OLD password using the saved hash
[postgres@pgdb02 data]$ cat pg_hba.conf | grep -i anu
host gebua raj,anu 0.0.0.0/0 md5
[postgres@pgdb02 data]$
[postgres@pgdb02 data]$ pg_ctl reload -D /pgData/pgsql17/data/
server signaled
[postgres@pgdb02 data]$
[postgres@pgdb02 data]$ PGPASSWORD='anu' psql -h 192.168.2.23 -d gebua -U anu
psql (17.10)
Type "help" for help.
gebua=>
1. Show current hash
select rolpassword from pg_authid where rolname='anu';
--- OR ---
postgres=# SELECT usename, passwd FROM pg_shadow WHERE usename = 'anu';
usename | passwd
---------+---------------------------------------------------------------------------------------------------------------------------------------
anu | SCRAM-SHA-256$4096:mXGdfJgtLi3531WT+4S93g==$wlYQ1SLzh0aNcESBEQ7jVgLkQqr4F6d674xQsNpSo1U=:WRudEkJcQvuHqcOpTgqWf6wQlizqPcS9MJ6lyT2AsyI=
(1 row)
postgres=#
2. Change password (simulate "forgot to save old one")
postgres=# ALTER USER anu WITH PASSWORD 'temporary_new_pass';
ALTER ROLE
postgres=#
3. "Restore" old password using saved hash string
[postgres@pgdb02 ~]$ PGPASSWORD='anu' psql -h 192.168.2.23 -d gebua -U anu
psql: error: connection to server at "192.168.2.23", port 5432 failed: FATAL: password authentication failed for user "anu"
[postgres@pgdb02 ~]$
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# ALTER USER anu WITH PASSWORD 'SCRAM-SHA-256$4096:mXGdfJgtLi3531WT+4S93g==$wlYQ1SLzh0aNcESBEQ7jVgLkQqr4F6d674xQsNpSo1U=:WRudEkJcQvuHqcOpTgqWf6wQlizqPcS9MJ6lyT2AsyI=';
ALTER ROLE
postgres=#
4. Confirm hash matches original
postgres=# SELECT usename, passwd FROM pg_shadow WHERE usename = 'anu';
usename | passwd
---------+---------------------------------------------------------------------------------------------------------------------------------------
anu | SCRAM-SHA-256$4096:mXGdfJgtLi3531WT+4S93g==$wlYQ1SLzh0aNcESBEQ7jVgLkQqr4F6d674xQsNpSo1U=:WRudEkJcQvuHqcOpTgqWf6wQlizqPcS9MJ6lyT2AsyI=
(1 row)
postgres=#
5. Login test — original plaintext password works again
[postgres@pgdb02 ~]$ PGPASSWORD='anu' psql -h 192.168.2.23 -d gebua -U anu
psql (17.10)
Type "help" for help.
gebua=> \c
You are now connected to database "gebua" as user "anu".
gebua=>
Note: Resetting the password does not extend the account's validity. The password will still expire unless you update it using one of the following commands
ALTER USER raj VALID UNTIL '2027-08-06'; ---- OR ------ ALTER USER raj VALID UNTIL 'infinity';Note that VALID UNTIL only controls the time window — it never touches the password hash itself. Extending expiry, without also running ALTER USER ... PASSWORD, leaves the original password value completely unchanged. Postgres also deliberately shows the same generic authentication error for an expired account as for a wrong password, so failed logins don’t leak the cause.
11. session_user vs. current_user
These two functions answer different questions:
- session_user — the role that actually authenticated the connection. Fixed for the whole session.
- current_user — the role whose privileges are being checked right now for the current statement. Changes with
SET ROLE, or inside a SECURITY DEFINER function.
---- Difference between session_user and current_user
postgres=# SELECT current_user, session_user;
current_user | session_user
--------------+--------------
postgres | postgres
(1 row)
postgres=# \c postgres sam
You are now connected to database "postgres" as user "sam".
postgres=>
postgres=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
sam | sam
(1 row)
postgres=>
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# SET ROLE sam;
SET
postgres=>
postgres=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
sam | postgres
(1 row)
postgres=>
postgres=> CREATE TABLE test(a int);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE test(a int);
^
postgres=>
postgres=> SET ROLE NONE;
SET
postgres=#
postgres=# SELECT current_user, session_user;
current_user | session_user
--------------+--------------
postgres | postgres
(1 row)
postgres=#
--- SET SESSION AUTHORIZATION
postgres=# SET SESSION AUTHORIZATION sam;
SET
postgres=>
postgres=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
sam | sam
(1 row)
postgres=> CREATE TABLE test(a int);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE test(a int);
^
postgres=> SET ROLE NONE;
SET
postgres=>
postgres=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
sam | sam
(1 row)
postgres=> RESET ROLE;
RESET
postgres=> SELECT current_user, session_user;
current_user | session_user
--------------+--------------
sam | sam
(1 row)
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# SELECT current_user, session_user;
current_user | session_user
--------------+--------------
postgres | postgres
(1 row)
postgres=#
12. search_path
gebua=#
postgres=# \c gebua
You are now connected to database "gebua" as user "postgres".
gebua=#
gebua=# show search_path;
search_path
-----------------
"$user", public
(1 row)
gebua=# \dt geb.*
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------
geb | dept | table | gebadm
geb | emp | table | gebadm
geb | payroll | table | gebadm
(3 rows)
gebua=# select * from geb.emp;
emp_id | emp_name | dept_id | salary
--------+----------+---------+----------
1 | Arjun | 1 | 75000.00
2 | Meena | 2 | 65000.00
3 | Kabir | 3 | 60000.00
(3 rows)
gebua=#
gebua=# select * from emp;
ERROR: relation "emp" does not exist
LINE 1: select * from emp;
^
gebua=#
gebua=# SET search_path to geb;
SET
gebua=# show search_path;
search_path
-------------
geb <------------
(1 row)
gebua=# select * from emp;
emp_id | emp_name | dept_id | salary
--------+----------+---------+----------
1 | Arjun | 1 | 75000.00
2 | Meena | 2 | 65000.00
3 | Kabir | 3 | 60000.00
(3 rows)
gebua=#
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/