Active connections

1

SELECT pid, usename, datname, application_name FROM pg_stat_activity WHERE usename = 'user_name';

Force user logout

1
2
3
4
5
6


REVOKE CONNECT ON DATABASE db_name FROM PUBLIC;

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

Change Password

1

ALTER ROLE rol_name WITH PASSWORD 'nueva_contraseña';

If you are changing the password for the role you are connected with, you can use:

1

\password role_name

To verify that the password has been changed successfully, you can check the roles in the pg_roles table:

1

SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname = 'rol_name';

Create users

Verify schema-level permissions. If the user has trouble accessing the table due to schema restrictions, you also need to grant permissions on the schema. Allow schema access:

1

GRANT USAGE ON SCHEMA public TO user_name;

Allow object creation in the schema

1

GRANT CREATE ON SCHEMA public TO user_name;

Delete users

If the role has dependencies (e.g., it owns any objects), you must reassign those objects before deleting the role. Use the REASSIGN OWNED command to reassign the objects to another role before removing it.

Reassign all objects from ‘rol1’ to ‘rol2’

1

REASSIGN OWNED BY rol1 TO rol2;

Remove all privileges granted to the role

1
2


DROP OWNED BY rol1;
DROP ROLE rol1;

If the role has active sessions, you will not be able to delete it until those sessions are closed. To force the termination of the connections:

1
2
3


SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.usename = 'rol1';

Change the ownership of their objects

1

ALTER TABLE table_name OWNER TO user ;

If you can’t transfer ownership, delete the objects first:

1
2


DROP TABLE table_name;
DROP DATABASE db_name;

Revoke permissions

Before deleting a user, it is good practice to revoke their permissions on all databases and schemas.

1
2
3
4


REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM user_name;
REVOKE ALL PRIVILEGES ON DATABASE db_name FROM user_name;

DROP ROLE user_name;

PostgreSQL does not allow deleting a user if they own tables, databases, sequences, etc. You must transfer or delete these objects first.

1
2


SELECT tablename FROM pg_tables WHERE tableowner = 'user_name';
SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = 'user_name');

Active connections#

Force user logout#

Change Password#

Create users#

Delete users#

Change the ownership of their objects#

Revoke permissions#