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.
Step 1: Create a User
-- 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
Step 2: Change User Password
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=>
Step 3: Grant Database Access
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=>
Step 4: Expire User Password
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=#
Step 6: Lock User Account
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=#
Step 7: Unlock User Account
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=#
Step 8: Create Schema
[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=#
Step 9: Create Roles & Users
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=#
Step 12: Grant RW Privileges
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=#
Step 13: Grant RO Privileges
-- 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=#
Step 15: Testing
-- 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/