​
Creating a PostgreSQL User with Custom Permissions

This section guides you through the process of creating a PostgreSQL user with specific permissions. The user will have read-only access to all existing and future tables, the ability to create new tables, and read-write access to any tables they create.

​
Prerequisites

Ensure you have administrative access to your PostgreSQL database to execute these commands. 
-- Create user
CREATE USER your_username WITH PASSWORD 'your_password';

-- Grant connect to database
GRANT CONNECT ON DATABASE your_database TO your_username;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO your_username;

-- Grant read-only access to all current and future tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_username;

-- Allow user to create tables
GRANT CREATE ON SCHEMA public TO your_username;

​
Step by step:

1

Create a user

First, connect to your PostgreSQL database using a superuser account or another account with sufficient privileges to create roles and grant permissions.
CREATE USER your_username WITH PASSWORD 'your_password';
Replace your_username and your_password with your desired username and password.
2

Grant Connect Permission

Grant the user the ability to connect to the database.
GRANT CONNECT ON DATABASE your_database TO your_username;
Replace your_database with the name of your database.
3

Grant Usage on Schema

Allow the user to use the default schema, typically public.
GRANT USAGE ON SCHEMA public TO your_username;
Replace your_username with the username you created in Step 1.
4

Grant Read-Only Access to All Current and Future Tables

Grant the user select (read) permission on all current tables and set default permissions for future tables in the schema.
-- Grant select on all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_username;

-- Set default select permission for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_username;
Replace your_username with the username you created in Step 1.
5

Allow User to Create Tables

While PostgreSQL does not directly offer a privilege to allow a user to only create tables, you can achieve this by granting the user the CREATE privilege on the schema.
GRANT CREATE ON SCHEMA public TO your_username;
This allows the user to create new objects within the schema, including tables.

​
Additional Considerations

  • If the database schema changes or if tables are moved between schemas, you may need to adjust permissions accordingly.
  • Regularly review user permissions to ensure they align with current security policies and database usage patterns.