Below are some common ways to grant access to a PostgreSQL user:
- Grant CONNECT to the database:
GRANT CONNECT ON DATABASE database_name TO username;
- Grant USAGE on schema:
GRANT USAGE ON SCHEMA schema_name TO username;
3. Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;
4. Grant all privileges on all tables in the schema:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;
5. Grant all privileges on all sequences in the schema:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;
6. Grant all privileges on the database:
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
7. Grant permission to create database:
ALTER USER username CREATEDB;
8. Make a user superuser:
ALTER USER myuser WITH SUPERUSER;
9. Remove superuser status:
ALTER USER username WITH NOSUPERUSER;
Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default.
For example:
ALTER DEFAULT PRIVILEGES
FOR USER username
IN SCHEMA schema_name
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;
To list all privileges a PostgreSQL user has, you can use various queries to access this information.
-
Table Permissions: You can query table permissions using:
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'YOUR_USER';
-
Ownership: To check ownership, you can use:
SELECT * FROM pg_tables WHERE tableowner = 'YOUR_USER';
-
Schema Permissions: For schema permissions, you can run:
SELECT r.usename AS grantor, e.usename AS grantee, nspname, privilege_type, is_grantable FROM pg_namespace JOIN LATERAL (SELECT * FROM aclexplode(nspacl) AS x) a ON true JOIN pg_user e ON a.grantee = e.usesysid JOIN pg_user r ON a.grantor = r.usesysid WHERE e.usename = 'YOUR_USER';
-
List User Accounts and Roles: To see all user accounts and roles, you can use:
\du
-
List Databases: To list all databases and their access privileges, you can run:
\l
By executing these queries in your PostgreSQL environment, you will be able to view all the privileges that a postgres user has on tables, schemas, and databases.