Creating users in postgres

Creating a user

CREATE USER myusername WITH PASSWORD 'mypassword';

Assigning read only permissions

Check if a user has permissions on a table

SELECT * from information_schema.table_privileges WHERE grantee = 'read_only_user' and table_name = 'users';

Grant read only permissions

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

Whenever the user ted creates a table, it will grant select rights for read_only_user.

ALTER DEFAULT PRIVILEGES
FOR USER ted
IN SCHEMA public
GRANT SELECT ON TABLES TO read_only_user;

Assigning read and write permissions

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tom;

ALTER DEFAULT PRIVILEGES
FOR USER admin
IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO tom;

Deleting a user

Assign the object ownership from the error detail to another user.

REASSIGN OWNED BY tom TO harry;

Remove the database object connections to the user with:

DROP OWNED BY tom;

At the moment, the user no longer has any dependencies. To drop the user, run

DROP USER tom;

Updating a user password

ALTER USER tom WITH PASSWORD 'fvfvfvafdfgff';

To give a user access to the postgres pg_stat_activity table, execute the command below from a superuser account.

GRANT pg_read_all_stats TO username;

You may need to kill a process like this.

SELECT pg_cancel_backend(31809);

You need to this permission.

GRANT pg_signal_backend TO brian;

Granting read permissions on sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO myuser;

If you want to grant the user the ability to modify sequences directly (e.g., to reset them), you can include the UPDATE permission as well:

GRANT SELECT, USAGE, UPDATE ON ALL SEQUENCES IN SCHEMA public TO myuser;