Create Super User or Update User to Super User in PostgreSQL

In this tutorial, we are going to first create user. Then, upgrade it as super user. Then, we directly create super user.

1. Create user as super user.

postgres=#

create user pg_admin with password 'test@123' SUPERUSER;

To verify run the \du command in psql tool

How to get long running queries in PostgreSQL

To get a list of running queries in PostgreSQL which are ordered by how long they have been executing. We can get long running queries by calculating time difference between query star time and current time stamp.

select current_timestamp – query_start as runtime, datname,pid, usename, query from pg_stat_activity where query <> ‘idle’
order by 1 desc;

For old versions(pre 9.2)

select current_timestamp - query_start as runtime, datname,pid, usename, current_query from pg_stat_activity where current_query<>'idle' order by 1 desc;

To get the queries running greater than 10 minutes in PostgreSQL:

How to get running queries in PostgreSQL

In this article, we can get the list of users running on PostgreSQL and their queries. Also, we can see particular user running queries and idle users in PostgreSQL.

To get list of users current running.

select usename from pg_stat_activity; To get list of users current running. But, not idle sessions.

Find List of active user connections to PostgreSQL

If we want to get list of users connected to the PostgreSQL server, use system view pg_stat_activity. It contains information like database name,process id, username, application name, client ip address, query start time, which query user is running.

If we want to all information about all users connected to the PostgreSQL, run below query.

select * from pg_stat_activity;

Output:

Find List of active user connections to PostgreSQL

Drop user in PostgreSQL

Drop user means is easy process in PostgreSQL. But, if the user owns database or its objects(table, views, stored procedure, …) it troughs error. To drop user, we must have superuser privilege. or createrole privilege.

Syntax:

DROP USER user_name;

Example:

postgres=# drop user federer; DROP ROLE

But, when user owns database or database objects(tables, views, stored procedure,…) it through following error.

bank=# drop user elchur; ERROR: role "elchur" cannot be dropped because some objects depend on it DETAIL: owner of database xyz privileges for table accounts

Solution: Reassign ownership to other users or restrict the user to login into database server.

1. Reassign ownership to other user.

REASSIGN OWNED BY elchur TO anil;

With the above command, whatever user ‘elchur’ is having permissions are assigned to user ‘anil’. After the above command, we can drop the login.

2. The easiest solution for this scenario is disallow the user from connecting.

alter user elchur no login;

After running above command, try to login. Then, you will receive error like below.

psql -U elchur postgres Password for user elchur: psql: FATAL: role "elchur" is not permitted to log in

Grant Access to all objects in schema PostgreSQL

Sometimes we have to grant access to all objects(tables, views, stored procedures,….) of schema. This can be done with the following command.

GRANT SELECT ON ALL TABLES IN SCHEMA accounts to john;

To grant this access, user must have GRANT privileges on the SCHEMA. But, not for superusers.
To Create user, we must be superuser or createrole or createuser privilege.

Create schema in PostgreSQL

Schema is nothing to separate groups of tables into their own names. Schema is database specific. Single schema can be present in multiple database in PostgreSQL.

Syntax:

CREATE SCHEMA schema_name;

Examples:

CREATE SCHEMA finance; CREATE SCHEMA HR; CREATE SCHEMA sales; CREATE SCHEMA transport; CREATE SCHEMA accounts;

Kill PostgreSQL session

Sometimes we have to kill User sessions on PostgreSQL database server for various reasons. We can kill session with PostgreSQL function pg_terminate_backend(). To kill the session, we have to find out user sessions by running following query. If we know the user process id(pid), then execute following query.

SELECT pg_terminate_backend(41864) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'postgres';

Step1: Get the process ids of the user.

FATAL: too many connections for role PostgreSQL

If we receive error “too many connections for role” when, we try to connect to PostgreSQL database. Then it may be allowed connections list is set or connection limit set to zero.

FATAL  too many connections for role PostgreSQL

Why this error throughs?

1. Check the number of active connection for the user with which are trying to login.

How to get the table size in PostgreSQL

We can determine table size by running following query will give the size of table accounts of the datbase ‘bank’

bank=# select pg_relation_size('accounts'); pg_relation_size ------------------ 8192

Or run the command \dt+ accounts

To get table sizes by order use following query

Powered by k2schools