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;
Top comments (0)