PostgreSQL Users/Roles and Privilages

-

Roles:

                  PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up. 
                 Roles can own database objects (for example, tables and functions) and can assign privileges on those objects to other roles to control who has access to which objects.
                 Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

Role Attributes:

CREATEDB         NOCREATEDB
CREATEROLE NOCREATEROLE
LOGIN           NOLOGIN
SUPERUSER          NOSUPERUSER
INHERIT NOINHERIT
BYPASSRLS         NOBYPASSRLS
REPLICATION NOREPLICATION
PASSWORD
ENCRYPTED PASSWORD
CONNECTION LIMIT
VALID UNTIL

Examples:

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |


postgres=# \l
                                                                                   List of databases
      Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                                                                                                      | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                                                                                                     | postgres=CTc/postgres
(3 rows)

postgres=# create role role_dba with superuser createdb createrole replication bypassrls inherit;
CREATE ROLE
postgres=# create user dba with password 'dba';
CREATE ROLE
postgres=# \du+
                                                 List of roles
 Role name |                                Attributes                                | Member of | Description
-----------+--------------------------------------------------------------------------+-----------+-------------
 dba       |                                                                          | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS               | {}        |
 role_dba  | Superuser, Create role, Create DB, Cannot login, Replication, Bypass RLS | {}        |

How to assign Role to User:

postgres=# grant role_dba to dba;
GRANT ROLE
postgres=# \du+
                                                  List of roles
 Role name |                                Attributes                                | Member of  | Description
-----------+--------------------------------------------------------------------------+------------+-------------
 dba       |                                                                          | {role_dba} |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS               | {}         |
 role_dba  | Superuser, Create role, Create DB, Cannot login, Replication, Bypass RLS | {}         |
 

How to grant access to users in PostgreSQL?

Here are some common statement to grant access to a PostgreSQL user:

Grant CONNECT to the database:

GRANT CONNECT ON DATABASE database_name TO username;

Grant USAGE on schema:

GRANT USAGE ON SCHEMA schema_name TO username;

Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

Grant all privileges on all tables in the schema:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

Grant all privileges on all sequences in the schema:

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;

Grant all privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Grant permission to create database:

ALTER USER username CREATEDB;

Make a user superuser:

ALTER USER myuser WITH SUPERUSER;

Remove superuser status:

ALTER USER username WITH NOSUPERUSER;

Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default. For example:

ALTER DEFAULT PRIVILEGES
FOR USER username
IN SCHEMA schema_name
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

How to show particular user table privileges:


postgres=# select table_schema,table_name,privilege_type from information_schema.role_table_grants where grantee='u2';

 table_schema | table_name | privilege_type
--------------+------------+----------------
 public       | customers  | SELECT
 public       | customers  | UPDATE


Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportion...
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index,...
Read more

PostgreSQL Reporting Tools(Pgbadger) Installation & Configuration

-
PgBadger:                 The PostgreSQL log analyzer “pgBadger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file.                  The report it generates shows all information found in a nice and easy to read report format.  These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system. FEATURES:  pgBadger reports everything about your SQL queries:         Overall statistics.         The most frequent waiting queries.         Queries that waited the most.         Queries generating the most temporary files.         Queries generating the largest temporar...
Read more