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:

GRANT CONNECT ON DATABASE your_database TO your_user;

2. Grant USAGE on schema:

GRANT USAGE ON SCHEMA your_schema TO your_user;

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

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

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

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

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

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

6. Grant all privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE your_database TO your_user;

7. Grant permission to create database:

ALTER USER your_user CREATEDB;

8. Make a user superuser:

ALTER USER myuser WITH SUPERUSER;

9. Remove superuser status:

ALTER USER your_user WITH NOSUPERUSER;

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:

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

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.