Limit PostgreSQL user access using schema and views

To limit PostgreSQL access to specific tables and columns you can use schema and selective GRANT statements.

Refer to the following instructions on how to limit access to certain tables.

However, if you need to limit access to specific columns you may need to use a combination of schemas and views to restrict access. You may also to do this if your database has over 1000 tables and you are attempting to connect to Chartio.

1. Connect to your PostgreSQL database using psql or pgadmin. Run the following to return a list of tables in your database.

   sudo -u postgres psql c databasename;

2. Create a schema specific to Chartio’s read only user run the following:

   CREATE SCHEMA chartio_read_only; CREATE ROLE chartio_schema_user LOGIN PASSWORD ‘secure_password’; GRANT CONNECT ON DATABASE databasename TO chartio_schema_user; GRANT USAGE ON SCHEMA chartio_read_only TO chartio_schema_user;

3. Take a look at the database relations using the d command.

   d

   Schema | Name          | Type  | Owner ———-+————————-+———-+————– public | Accounts      | table | postgres public | Visitors      | table | postgres public | Users         | table | postgres public | Subscriptions | table | postgres

4. Your Accounts table has sensitive information in it, and you want Chartio to access only the Account_ID and Date__Created columns._ Run the following to add a view to the chartio_read_only schema and grant access to that view to the chartio_schema_user.

   CREATE VIEW chartio_read_only.”Accounts” AS SELECT Account_ID, Date_Created FROM Accounts; GRANT SELECT ON chartio_read_only.”Accounts” TO chartio_schema_user;

5. Grant permissions to select all data from the Visitors table.

   CREATE VIEW chartio_read_only.”Visitors” AS SELECT * FROM Visitors; GRANT SELECT ON chartio_read_only.”Visitors” TO chartio_schema_user;

Now if you connect the chartio_schema_user to your database it will only be able to access the two limited views you have granted to the schema.

For more information, consult the PostgreSQL documentation.