Create new user and grand access to database on PostgresQL

Published on January 2, 2023

postgres=# CREATE USER new_user WITH ENCRYPTED PASSWORD 'new_password';
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
postgres=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;

Above we connected to database postgres under superuser postgres. We create user with name new_user and password new_password. Grant access rights to the database. Below the explanations each of these steps.

Create new user #

% psql -U postgres
postgres=# CREATE USER new_user WITH ENCRYPTED PASSWORD 'new_password';
CREATE ROLE

Connect to database as superuser using psql utility. Perform the request as show above. Replace username and password with your own values. If operation succeed, CREATE will be printed. User created, but has no access to database.

Grant access to database #

User can connect to database with credentials, but can not read data from tables and sequences. If you try to select rows from a table you will get error: ERROR: permission denied for table ...

Grant access to tables #

postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
GRANT

This command grant all access rights to all tables in schema public in current database. Now all operations on tables allowed to user: select, update, delete and alter also allowed.

Grant access to sequences #

postgres=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;
GRANT

Grant all privileges on all sequences in schema schema in database you connected to.