Tag Archives: PostgreSQL security

PostgreSQL pg_hba.conf

 

PostgreSQL pg_hba.conf Guide

Table of Contents

1. What is pg_hba.conf?
2. Authentication Methods in pg_hba.conf
3. Create Database and User
4. Contents of pg_hba.conf
5. Play with peer
6. Play with trust
7. Play with reject
8. Restrict by User
9. Restrict by Network
10. Verify logs


1. What is pg_hba.conf?


Location: usually inside PostgreSQL data directory (/var/lib/pgsql/<version>/data/pg_hba.conf or /pgData/pgsql15/data/pg_hba.conf depending on your setup).

HBA = Host-Based Authentication.

Format:

# TYPE  DATABASE  USER  ADDRESS        METHOD
TYPE: local, host, hostssl, hostnossl

DATABASE: which DBs (e.g. all, postgres, mydb)
USER	: which roles (e.g. all, myuser)
ADDRESS	: client IP/CIDR (127.0.0.1/32, 192.168.2.0/24)

METHOD	: authentication method (trust, md5, scram-sha-256, peer, reject, cert)

OPTIONS	: optional settings (e.g., clientcert=1)


2. Authentication Methods in pg_hba.conf

trust: 

No password needed. Anyone who can connect to the server is trusted.
Use: testing only.
Not secure in production.


Example: host    all    all    127.0.0.1/32    trust

md5

Password required, stored as MD5 hash. Legacy support.
Weaker security, avoid if possible.


Example: host    all    all    127.0.0.1/32    md5

scram-sha-256

Password required, stored as salted SCRAM-SHA-256 hash.
Stronger than MD5.
Recommended method for production.

Example: host    all    all    127.0.0.1/32    scram-sha-256


peer
 
Works for local connections only.
The OS user name must match the PostgreSQL role.
Good for local scripts/services under same user.


Example:
local   all    all                     peer

reject
 
Explicitly denies access.
Useful for blocking unwanted connections (like a firewall rule).

Example:
host    all    all    0.0.0.0/0       reject

cert

Requires SSL/TLS client certificate.
PostgreSQL role must match certificate username.
Very secure for enterprise / production with SSL.
 
Example:
hostssl all   all    192.168.1.0/24   cert clientcert=1


3. Create Database and User

postgres=# CREATE USER teja WITH PASSWORD 'teja123';
CREATE ROLE
postgres=# CREATE DATABASE orcl OWNER teja;
CREATE DATABASE
postgres=#


4. Contents of pg_hba.conf

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     scram-sha-256

[postgres@pg17 ~]$


5. Play with peer

Works for local connections only. The OS user name must match the PostgreSQL role.

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres

local   all             postgres                                peer


# Allow local password access for all
local   all             all                                     scram-sha-256

[postgres@pg17 ~]$

[postgres@pg17 ~]$ psql -U teja -d orcl
 Password for user teja: <---- it's asking password 

psql (15.13)
Type "help" for help.

orcl=> exit
[postgres@pg17 ~]$

 Password Not asking for user postgres 
[postgres@pg17 ~]$ psql -U postgres -d orcl
psql (15.13)
Type "help" for help.

orcl=#


6. Play with trust

No password needed. Anyone who can connect to the server is trusted.
Use: testing only. Not secure in production.

Change pg_hba.conf:

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all

local   all             all                                     trust

[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ psql -U teja -d orcl  It won’t ask for a password. 
psql (15.13)
Type "help" for help.

orcl=>


7. Play with reject

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all

local   all             all                                     reject

[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ psql -U teja -d orcl

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  pg_hba.conf rejects connection for host "[local]", user "teja", database "orcl", no encryption

[postgres@pg17 ~]$


8. Restrict by User

Allow only user teja, block everyone else:

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     trust

# Allow IPv4 localhost

host    orcl            teja             192.168.2.31/32         scram-sha-256
host    orcl            all              192.168.2.31/32         reject


[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ psql -U teja -d orcl -h 192.168.2.31
Password for user teja:
psql (15.13)
Type "help" for help.

orcl=> \conninfo
You are connected to database "orcl" as user "teja" on host "192.168.2.31" at port "5432".
orcl=>
orcl=> exit
[postgres@pg17 ~]$


[postgres@pg17 ~]$ psql -U postgres -d orcl -h 192.168.2.31

psql: error: connection to server at "192.168.2.31", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "postgres", database "orcl", no encryption

[postgres@pg17 ~]$


Notice: Only user Teja able to connect where as user postgres not able to connect


9. Restrict by Network

We want to block connections from IP 192.168.2.0/24 

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     trust

# Allow IPv4 localhost

host    all             all             192.168.2.0/24          reject

[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ 
[postgres@pg17 ~]$ psql -U teja -d orcl -h 192.168.2.31

psql: error: connection to server at "192.168.2.31", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "teja", database "orcl", no encryption

[postgres@pg17 ~]$


10. Verify logs

postgres=# SELECT * FROM pg_hba_file_rules;
 line_number | type  | database | user_name  |   address    |     netmask     |  auth_method  | options | error
-------------+-------+----------+------------+--------------+-----------------+---------------+---------+-------
           4 | local | {all}    | {postgres} |              |                 | peer          |         |
           7 | local | {all}    | {all}      |              |                 | trust         |         |
          11 | host  | {orcl}   | {teja}     | 192.168.2.31 | 255.255.255.255 | scram-sha-256 |         |
          12 | host  | {orcl}   | {all}      | 192.168.2.31 | 255.255.255.255 | reject        |         |
(4 rows)

postgres=#

[postgres@pg17 ~]$ tail -f /pgData/pgsql15/data/log/postgresql-Tue.log
2025-09-16 04:08:37.244 EDT [8294] LOG:  database system was shut down at 2025-09-16 04:08:32 EDT
2025-09-16 04:08:37.251 EDT [8289] LOG:  database system is ready to accept connections
2025-09-16 04:11:27.459 EDT [8289] LOG:  received SIGHUP, reloading configuration files
2025-09-16 04:11:31.842 EDT [8401] FATAL:  pg_hba.conf rejects connection for host "[local]", user "teja", database "orcl", no encryption
2025-09-16 04:13:37.568 EDT [8292] LOG:  checkpoint starting: time
2025-09-16 04:13:37.573 EDT [8292] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.002 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2025-09-16 04:16:15.863 EDT [8289] LOG:  received SIGHUP, reloading configuration files
2025-09-16 04:16:52.318 EDT [8474] FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "teja", database "orcl", no encryption
2025-09-16 04:27:15.932 EDT [8289] LOG:  received SIGHUP, reloading configuration files
2025-09-16 04:27:50.593 EDT [8613] FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "postgres", database "orcl", no encryption

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/

PostgreSQL User Management

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:

 

ShortFull PrivilegeExample GRANT
rSELECTGRANT SELECT ON employees TO hr_user;
aINSERTGRANT INSERT ON employees TO hr_user;
wUPDATEGRANT UPDATE ON employees TO hr_user;
dDELETEGRANT DELETE ON employees TO hr_user;
DTRUNCATEGRANT TRUNCATE ON employees TO hr_user;
xREFERENCESGRANT REFERENCES ON employees TO hr_user;
tTRIGGERGRANT TRIGGER ON employees TO hr_user;
RRULEGRANT 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/