PostgreSQL User Management
Create Users, Groups and Schema Paths for PostgreSQL
Table of Contents
Step 1: Create a User
Step 2: Change User Password
Step 3: Grant Database Access
Step 4: Expire Password
Step 5: Set Password Never Expire
Step 6: Lock Account
Step 7: Unlock Account
Step 8: Create Schema
Step 9: Create Roles & Users
Step 10: Assign Ownership
Step 11: Grant Schema Privileges
Step 12: Grant RW Privileges
Step 13: Grant RO Privileges
Step 14: Assign Roles to Users
Step 15: Testing
Step 16: Set Schema Search Path
Step 17: Groups
Step 18: PostgreSQL — Table-View Privileges
Note: Users are just roles with LOGIN privilege; roles without LOGIN cannot connect.
-- Option 1: Using CREATE USER CREATE USER john WITH PASSWORD 'mypassword'; -- Option 2: Using CREATE ROLE with LOGIN CREATE ROLE john LOGIN PASSWORD 'mypassword'; postgres=# CREATE USER john WITH PASSWORD 'mypassword'; CREATE ROLE postgres=# CREATE ROLE john LOGIN PASSWORD 'mypassword'; ERROR: role "john" already exists
Passwords can be changed by either an admin or the user. By Admin: postgres=# ALTER USER john WITH PASSWORD 'newpassword'; ALTER ROLE postgres=# -- OR -- postgres=# \password john Enter new password for user "john": Enter it again: postgres=# By User (self-service): From the psql prompt: [postgres@pg17 ~]$ psql -h 192.168.2.31 -U john -d mydb -W Password: mydb=> \conninfo You are connected to database "mydb" as user "john" on host "192.168.2.31" at port "5432". mydb=> \password Enter new password for user "john": Enter it again: mydb=>
To allow a user to connect to a database: postgres=# GRANT CONNECT ON DATABASE mydb TO john; GRANT postgres=# Verify user login: -- Connect as user [postgres@pg17 ~]$ psql -h 192.168.2.31 -U john -d mydb -W Password: -- Check current user mydb=> SELECT CURRENT_USER; current_user -------------- john (1 row) mydb=> mydb=> select session_user; session_user -------------- john (1 row) -- Connection info mydb=> \conninfo You are connected to database "mydb" as user "john" on host "192.168.2.31" at port "5432". mydb=>
postgres=# ALTER USER john VALID UNTIL '2025-09-11'; ALTER ROLE postgres=# postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- john | Password valid until 2025-09-11 00:00:00-04 | {} | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres=#
Step 5: Set Password to Never Expire
postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- john | Password valid until 2025-09-11 00:00:00-04 | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} -- Without changing existing password postgres=# ALTER USER john VALID UNTIL 'infinity'; ALTER ROLE postgres=# -- With changing existing password postgres=# ALTER USER john WITH PASSWORD 'newpassword' VALID UNTIL 'infinity'; ALTER ROLE postgres=# postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- john | Password valid until infinity | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=# -- Set to future date postgres=# ALTER USER john VALID UNTIL '2025-12-31'; ALTER ROLE postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- john | Password valid until 2025-12-31 00:00:00-05 | {} | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres=#
postgres=# ALTER USER john NOLOGIN; ALTER ROLE postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- john | Cannot login +| {} | | Password valid until infinity | | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres=#
postgres=# ALTER USER john LOGIN; ALTER ROLE postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- john | Password valid until infinity | {} | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | postgres=#
[postgres@pg17 ~]$ psql psql (15.13) Type "help" for help. postgres=# \c mydb You are now connected to database "mydb" as user "postgres". mydb=# mydb=# CREATE SCHEMA BLP; CREATE SCHEMA mydb=# \dn List of schemas Name | Owner --------+------------------- blp | postgres public | pg_database_owner (2 rows) mydb=#
postgres=# CREATE USER "BLP" WITH PASSWORD 'blp'; CREATE ROLE postgres=# postgres=# CREATE ROLE blp_rw NOLOGIN; CREATE ROLE postgres=# CREATE ROLE blp_ro NOLOGIN; CREATE ROLE postgres=# postgres=# CREATE USER alice WITH PASSWORD 'alice123'; CREATE ROLE postgres=# CREATE USER bob WITH PASSWORD 'bob123'; CREATE ROLE postgres=# CREATE USER charlie WITH PASSWORD 'charlie123'; CREATE ROLE postgres=# postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- BLP | | {} | alice | | {} | blp_ro | Cannot login | {} | blp_rw | Cannot login | {} | bob | | {} | charlie | | {} | john | Password valid until 2025-12-31 00:00:00-05 | {} | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | trduser | | {} | postgres=#
Step 10: Assign Ownership on Schema
-- Please do NOT grant this privillege, Owner can drop the schema, change privileges, and has full control over all objects inside. postgres=# \c mydb You are now connected to database "mydb" as user "postgres". mydb=# mydb=# \dn List of schemas Name | Owner --------+------------------- blp | postgres public | pg_database_owner (2 rows) mydb=# ALTER SCHEMA BLP OWNER TO "BLP"; ALTER SCHEMA mydb=# mydb=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+-------------------+----------------------------------------+------------------------ blp | BLP | | public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema | | =U/pg_database_owner | (2 rows) mydb=#
Step 11: Grant Schema Privileges to Owner
-- Full control on schema: usage + create GRANT USAGE, CREATE ON SCHEMA blp TO "BLP"; mydb=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+-------------------+----------------------------------------+------------------------ blp | BLP | | public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema | | =U/pg_database_owner | (2 rows) mydb=# GRANT USAGE, CREATE ON SCHEMA blp TO "BLP"; GRANT mydb=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+-------------------+----------------------------------------+------------------------ blp | BLP | BLP=UC/BLP | public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema | | =U/pg_database_owner | (2 rows) mydb=#
USAGE → allows the role to see the schema and its objects. -- Grant schema access without CREATE GRANT USAGE ON SCHEMA BLP TO blp_rw; -- Grant DML on all existing tables GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA BLP TO blp_rw; -- Future tables ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blp_rw; mydb=# GRANT USAGE ON SCHEMA BLP TO blp_rw; GRANT mydb=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA BLP TO blp_rw; GRANT mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blp_rw; ALTER DEFAULT PRIVILEGES mydb=# mydb=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+-------------------+----------------------------------------+------------------------ blp | BLP | BLP=UC/BLP +| | | blp_rw=U/BLP | public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema | | =U/pg_database_owner | (2 rows) mydb=#
-- Grant schema access without CREATE GRANT USAGE ON SCHEMA BLP TO BLP_RO; -- Grant SELECT on all existing tables GRANT SELECT ON ALL TABLES IN SCHEMA BLP TO BLP_RO; -- Future tables ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT ON TABLES TO BLP_RO; mydb=# GRANT USAGE ON SCHEMA BLP TO BLP_RO; GRANT mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA BLP TO BLP_RO; GRANT mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT ON TABLES TO BLP_RO; ALTER DEFAULT PRIVILEGES mydb=# mydb=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+-------------------+----------------------------------------+------------------------ blp | BLP | BLP=UC/BLP +| | | blp_rw=U/BLP +| | | blp_ro=U/BLP | public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema | | =U/pg_database_owner | (2 rows) mydb=#
Step 14: Assign Roles to Users
mydb=# GRANT BLP_RW TO ALICE; GRANT ROLE mydb=# mydb=# GRANT BLP_RO TO BOB,CHARLIE; GRANT ROLE mydb=# mydb=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- BLP | | {} | alice | | {blp_rw} | blp_ro | Cannot login | {} | blp_rw | Cannot login | {} | bob | | {blp_ro} | charlie | | {blp_ro} | john | Password valid until 2025-12-31 00:00:00-05 | {} | postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | trduser | | {} | mydb=#
-- Login to BLP user on mydb database and create table on blp schema. [postgres@pg17 ~]$ psql -h 192.168.2.31 -U BLP -d mydb -W Password: psql (15.13) Type "help" for help. mydb=> \conninfo You are connected to database "mydb" as user "BLP" on host "192.168.2.31" at port "5432". mydb=> CREATE TABLE blp.employees ( mydb(> emp_id SERIAL PRIMARY KEY, mydb(> first_name VARCHAR(50), mydb(> last_name VARCHAR(50), mydb(> hire_date DATE, mydb(> salary NUMERIC(10,2) mydb(> ); CREATE TABLE mydb=> mydb=> INSERT INTO blp.employees (first_name, last_name, hire_date, salary) VALUES mydb-> ('John', 'Doe', '2023-01-15', 5000.00), mydb-> ('Jane', 'Smith', '2022-11-20', 6000.00), mydb-> ('Alice', 'Johnson', '2024-03-01', 5500.00); INSERT 0 3 mydb=> mydb=> select * from blp.employees; emp_id | first_name | last_name | hire_date | salary --------+------------+-----------+------------+--------- 1 | John | Doe | 2023-01-15 | 5000.00 2 | Jane | Smith | 2022-11-20 | 6000.00 3 | Alice | Johnson | 2024-03-01 | 5500.00 (3 rows) mydb=> mydb=> drop table blp.employees; DROP TABLE mydb=> -- Login to alice user on mydb database and update table on blp schema. [root@pg17 ~]# psql -h 192.168.2.31 -U alice -d mydb -W Password: psql (15.13) Type "help" for help. mydb=> \conninfo You are connected to database "mydb" as user "alice" on host "192.168.2.31" at port "5432". mydb=> mydb=> \du+ alice List of roles Role name | Attributes | Member of | Description -----------+------------+-----------+------------- alice | | {blp_rw} | mydb=> mydb=> \dt+ blp.* List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+-----------+-------+-------+-------------+---------------+------------+------------- blp | employees | table | BLP | permanent | heap | 8192 bytes | (1 row) mydb=> select * from blp.employees; emp_id | first_name | last_name | hire_date | salary --------+------------+-----------+------------+--------- 1 | John | Doe | 2023-01-15 | 5000.00 2 | Jane | Smith | 2022-11-20 | 6000.00 3 | Alice | Johnson | 2024-03-01 | 5500.00 (3 rows) mydb=> UPDATE blp.employees SET salary = CASE WHEN first_name = 'John' THEN 7000.00 WHEN first_name = 'Alice' THEN 6500.00 END WHERE first_name IN ('John', 'Alice'); UPDATE 2 mydb=> select * from blp.employees; emp_id | first_name | last_name | hire_date | salary --------+------------+-----------+------------+--------- 2 | Jane | Smith | 2022-11-20 | 6000.00 1 | John | Doe | 2023-01-15 | 7000.00 3 | Alice | Johnson | 2024-03-01 | 6500.00 (3 rows) mydb=> -- Note, we have granted only DML privilleges, hence create and alter table command failing mydb=> CREATE TABLE blp.departments ( mydb(> dept_id SERIAL PRIMARY KEY, mydb(> dept_name VARCHAR(100) NOT NULL, mydb(> location VARCHAR(100) mydb(> ); ERROR: permission denied for schema blp LINE 1: CREATE TABLE blp.departments ( ^ mydb=> mydb=> ALTER TABLE blp.employees mydb-> ADD COLUMN department VARCHAR(50); ERROR: must be owner of table employees mydb=> -- Login to bob user on mydb database and select table on blp schema. [postgres@pg17 ~]$ psql -h 192.168.2.31 -U bob -d mydb -W Password: psql (15.13) Type "help" for help. mydb=> \conninfo You are connected to database "mydb" as user "bob" on host "192.168.2.31" at port "5432". mydb=> mydb=> \du+ bob List of roles Role name | Attributes | Member of | Description -----------+------------+-----------+------------- bob | | {blp_ro} | mydb-> \dt+ blp.* List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+-----------+-------+-------+-------------+---------------+------------+------------- blp | employees | table | BLP | permanent | heap | 8192 bytes | (1 row) mydb=> select * from blp.employees; emp_id | first_name | last_name | hire_date | salary --------+------------+-----------+------------+--------- 2 | Jane | Smith | 2022-11-20 | 6000.00 1 | John | Doe | 2023-01-15 | 7000.00 3 | Alice | Johnson | 2024-03-01 | 6500.00 (3 rows) mydb=> mydb=> INSERT INTO blp.employees (first_name, last_name, hire_date, salary) VALUES mydb-> ('Bob', 'Williams', '2024-04-01', 5800.00), mydb-> ('Clara', 'Brown', '2024-05-10', 6200.00), mydb-> ('David', 'Lee', '2024-06-15', 5300.00); ERROR: permission denied for table employees mydb=>
Step 16: Set Schema Search Path
[postgres@pg17 ~]$ psql -h 192.168.2.31 -U BLP -d mydb -W Password: psql (15.13) Type "help" for help. mydb=> mydb=> \conninfo You are connected to database "mydb" as user "BLP" on host "192.168.2.31" at port "5432". mydb=> mydb=> \dt+ blp.* List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+-----------+-------+-------+-------------+---------------+------------+------------- blp | employees | table | BLP | permanent | heap | 8192 bytes | (1 row) mydb=> mydb=> select * from employees; ERROR: relation "employees" does not exist LINE 1: select * from employees; mydb=> mydb=> SET search_path to BLP; -- Temporarily for this session SET mydb=> select * from employees; emp_id | first_name | last_name | hire_date | salary --------+------------+-----------+------------+--------- 2 | Jane | Smith | 2022-11-20 | 6000.00 1 | John | Doe | 2023-01-15 | 7000.00 3 | Alice | Johnson | 2024-03-01 | 6500.00 (3 rows) mydb=> -- Make the Schema Default for the user BLP -- Permenant ALTER ROLE blp_owner SET search_path = BLP;
Step 17: Groups
postgres=# CREATE GROUP app_users; postgres=# ALTER GROUP app_users ADD USER alice; postgres=# ALTER GROUP app_users ADD USER bob; postgres=# ALTER GROUP app_users ADD USER charlie; postgres=# ALTER GROUP app_users DROP USER charlie; postgres=# ALTER GROUP app_users RENAME TO appusers; postgres=# DROP GROUP appusers; mydb=> 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 | {} blp_rw | 84444 | {84447} blp_ro | 84445 | {84448,84449} app_users | 84468 | {} (15 rows) mydb=>
Step 18: PostgreSQL Object-Level Privilege Summary
mydb=> \z blp.employees
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------+-------+-------------------+-------------------+----------
blp | employees | table | BLP=arwdDxt/BLP +| |
| | | blp_ro=r/BLP +| |
| | | blp_rw=arwd/BLP | |
(1 row)Example:
| Short | Full Privilege | Example GRANT |
|---|---|---|
| r | SELECT | GRANT SELECT ON employees TO hr_user; |
| a | INSERT | GRANT INSERT ON employees TO hr_user; |
| w | UPDATE | GRANT UPDATE ON employees TO hr_user; |
| d | DELETE | GRANT DELETE ON employees TO hr_user; |
| D | TRUNCATE | GRANT TRUNCATE ON employees TO hr_user; |
| x | REFERENCES | GRANT REFERENCES ON employees TO hr_user; |
| t | TRIGGER | GRANT TRIGGER ON employees TO hr_user; |
| R | RULE | GRANT RULE ON employees TO hr_user; (rarely used) |
Tip: the shorthand string you see in \z (for example arwdDxt) can be expanded by mapping each letter to the rows above, then converting them into one or more GRANT statements.
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
WhatsApp :
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/