How to grant access to users in PostgreSQL?

In PostgreSQL, you can grant access to users by using the GRANT statement. This statement allows you to assign various privileges to users or roles for specific database objects. Here's a basic overview of how you can grant access:

1. Grant CONNECT to the database:

1GRANT CONNECT ON DATABASE your_database TO your_user;
2

2. Grant USAGE on schema:

1GRANT USAGE ON SCHEMA your_schema TO your_user;
2

3. Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:

1GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO your_user;
2

4. Grant all privileges on all tables in the schema:

1GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA your_schema TO your_user;
2

5. Grant all privileges on all sequences in the schema:

1GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA your_schema TO your_user;
2

6. Grant all privileges on the database:

1GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;
2

7. Grant permission to create database:

1ALTER USER your_user CREATEDB;
2

8. Make a user superuser:

1ALTER USER myuser WITH SUPERUSER;
2

9. Remove superuser status:

1ALTER USER your_user WITH NOSUPERUSER;
2

The statements provided earlier are targeted towards modifying the default privileges for existing tables. To ensure that these privileges are automatically applied to newly created tables, it is necessary to utilize the ALTER DEFAULT PRIVILEGES statement. As an illustration:

1ALTER DEFAULT PRIVILEGES FOR USER your_user
2    IN SCHEMA your_schema
3    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_user;
4

Remember to replace placeholders like your_database, your_schema, your_user, and your_table with your actual database, schema, username, and table names. Also, be cautious when granting permissions, as improper permissions can pose security risks.