Menu
Nadzweb.com
Nadzweb.com

PostgreSQL – Granting access to users

Posted on May 15, 2020June 15, 2020 by admin

How to grant access to users in PostgreSQL?
Here are some common statement to grant access to a PostgreSQL user:

Grant CONNECT to the database
GRANT CONNECT ON DATABASE database_name TO username;

Grant USAGE on schema
GRANT USAGE ON SCHEMA schema_name TO username;

Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;

Grant all privileges on all tables in the schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;

Grant all privileges on all sequences in the schema
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;

Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Grant permission to create database
ALTER USER username CREATEDB;

Make a user superuser
ALTER USER myuser WITH SUPERUSER;

Remove superuser status
ALTER USER username WITH NOSUPERUSER;


Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default. For example:

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


  • postgres
  • sql
  • Leave a Reply Cancel reply

    Your email address will not be published. Required fields are marked *

    *
    To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
    Anti-spam image

    Tags

    .htaccess angular angular2 angular2-pipes angular4 angularjs apache bigdata blockchain children codeigniter computer graphics ethereum flot flot charts funny hadoop http javascript jquery kanban lena linux love math mathematics microsoft misc node js php phpframework php frameworks postgres pun-intended python react sass scrum scss silverstripe software ssl story valentines day wordpress

    Archives

    Recent Posts

    • Install only Postgres client 11 on Ubuntu 18.04
    • PostgreSQL – Granting access to users
    • Querying JSONB Postgres fields in SQLAlchemy
    • Angular – Writing unit tests for setTimeout in functions
    • Angular 6 – getting previous url from angular router
    ©2021 Nadzweb.com | Powered by WordPress & Superb Themes