How to Create a read-only User in Greenplum
Article ID: 295704
Updated On:
Products
Issue/Introduction
This article describes how to create a read-only user. The user (non-superuser) with the connect permission on the database is able to create or drop object on the database. This article also explains how do restrict them from doing so?
Environment
Resolution
By default any user that is allowed to connect to the database can create an object in the public schema, you can use the below query to check who can connect to the column "rolcanlogin":
SELECT * FROM pg_roles;
So, if you wish users to avoid creating a table, you will need to revoke first permission from the public through this command below after connecting to the database:
REVOKE ALL ON schema public FROM public;
This will make only gpadmin ( superuser ) create objects under the database in question, but if you wish another user to create an object in it as well, you would have to grant them separately or manually like
GRANT ALL ON schema public TO <username or rolename>;
Example
Let's take an example to understand the scenario better.
First, let's check who can create an object under a database in the public schema:
gpadmin=# select * from pg_namespace where nspname='public';
nspname | nspowner | nspacl
---------+----------+------------------------------------------------
public | 10 | {gpadmin=UC/gpadmin,a2=UC/gpadmin,=UC/gpadmin}
(1 row)
From the above example, the column "nspacl" shows an entry of "=UC/gpadmin" , note there is no value before the last "=""sign, which means anyone can create objects under this public schema.
Revoke the grant and verifying it, shows now no one can create the object under it:
gpadmin=# REVOKE ALL ON schema public FROM public;
REVOKE
Time: 299.719 ms
gpadmin=# select * from pg_namespace where nspname='public';
nspname | nspowner | nspacl
---------+----------+------------------------------------
public | 10 | {gpadmin=UC/gpadmin,a2=UC/gpadmin}
(1 row)
Time: 23.899 ms
gpadmin=#
Connecting as non-superuser a1 and attempting to create a table, throws out an error like below:
gpadmin:Fullrack@mdw $ psql -U a1 Password for user a1: Timing is on. Pager usage is off. psql (8.2.15) Type "help" for help. gpadmin=> create table test1111 as select * from pg_class; NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: no schema has been selected to create in gpadmin=>
As gpadmin (superuser), grant specific users/role to create the object under the schema:
gpadmin=# GRANT ALL ON schema public TO a1;
GRANT
Time: 125.443 ms
gpadmin=# select * from pg_namespace where nspname='public';
nspname | nspowner | nspacl
---------+----------+--------------------------------------------------
public | 10 | {gpadmin=UC/gpadmin,a2=UC/gpadmin,a1=UC/gpadmin}
(1 row)
Time: 22.500 ms
gpadmin=# \q
gpadmin:Fullrack@mdw $ psql -U a1
Password for user a1:
Timing is on.
Pager usage is off.
psql (8.2.15)
Type "help" for help.
gpadmin=> create table test1111 as select * from pg_class;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 508
Time: 3921.078 ms
Additional Information
Pivotal Greenplum Database (GPDB) all versions
Feedback
