📋Steps in this guide1/1
PostgreSQL User Management

Learn how to set PostgreSQL schema search_path and manage object-level privileges with GRANT and REVOKE. Includes examples, z output, and cheat sheet.
postgresql configurationintermediate
by PostgreSQL
13 views
Create Users, Groups and Schema Paths for PostgreSQL

Note: Users are just roles with LOGIN privilege; roles without LOGIN cannot connect. Step 1: Create a User Step 2: Change User Password Step 3: Grant Database Access Step 4: Expire User Password Step 5: Set Password to Never Expire Step 6: Lock User Account Step 7: Unlock User Account Step 8: Create Schema Step 9: Create Roles & Users Step 10: Assign Ownership on Schema Step 11: Grant Schema Privileges to Owner 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 Object-Level Privilege Summary Example: r a w d D x t R Tip: the shorthand string you see in (for example ) can be expanded by mapping each letter to the rows above, then converting them into one or more 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.
Code/Command (click line numbers to comment):

-- 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=#

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=#

-- 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=#

-- 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=#

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

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

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

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)
Comments (0)

Please to add comments
No comments yet. Be the first to comment!